VBA to protect/unprotect sheet...

[HURTMYPONY]

Hello, all!

I have created a sheet with cells that have specific cell ranges containing dropdown data validation lists and others with formulas and values, all of which need to be selectively protected.

Some of the other cells on the sheet must allow direct input of any value.

You folks might have a better approach for me, but I think I need:

• The specific cell ranges (data validation lists, formulas) to remain protected from users until a macro is called (via button).
• Unprotect those cells to allow the macro/VBA(s) script to work.
• Re-protect the specific cell ranges after the macro/VBA script(s) have run.

Some example ranges that are protected:

G3:H4
G6:H13
H15:I15
H18:H25
H27:I27

There are more ranges than above but this should give you a good idea of it.

I know how to manually protect the cells and I also know how to unprotect the whole sheet via VBA.

I guess I just don’t know how to use VBA to:

• Re-protect specific cell ranges.
• Allow the cells with data validation lists to only allow what is on the associated lists.

Can anyone please help me out?
I am also open to a better approach if I’ve over-complicated things…

I've attached an image of the sheet for visual reference.
The ranges highlighted in yellow are a combination of cells with data validation - lists and formulas that need protection before and after the scripts run.

 

[jkpieterse]

Cell protection works like this:

• Each cell has a Locked state (which is turned on by default)
• This locked state does not do anything, unless you protect the worksheet. Then all locked cells cannot be changed.
• When protecting the sheet you can set options which you want to allow the user to do, such as changing formatting and the like.

When you set up validation, the Style drop-down on the third tab determines how strict the validation works. It defaults to Stop, which disallows wrong entries.

 

[mumps]

Start by unlocking all the cells in the worksheet. Then lock all the cells in the ranges that need protection. Place your code between these lines of code:

ActiveSheet.Unprotect "YourPasswordHere"

and

ActiveSheet.Protect "YourPasswordHere"

Change the text in red to suit your needs.

 

[HURTMYPONY]

My apologies for the late reply - thank you very much for the solution!

I think my initial issue was caused by protecting the cells with validation list dropdowns.

When those cells are protected, they do not allow users to access the values in its associated dropdown list.

I went ahead and removed the protection from those specific cells and am relying on the built-in data validation protection with "Ignore Blank" unchecked.

+++

This works - with MANUAL entry, the data validation only allows values currently on the validation list...

But an unfortunate side effect with this is that cells protected this way still permit a user to copy-paste ANY value into the cell by choosing "Keep Source Formatting" when they paste it into the cell.

It also removes the data validation.

Is this behavior natural or have I overlooked something?

I'd really like to restrict the entries of values in those cells to ONLY what is in the dropdown...

 

[jkpieterse]

As far as I know, a fairly recent update prevents the DV rule to disappear on paste, provided the sheet is protected. But it still allows invalid values to be pasted.

 

[HURTMYPONY]

Thank you for the reply...

Since the sheet is more of a custom data entry form than your typical list, my other idea is to just have the form recreate the data validation each time the submission script is run.

That way, if a user unintentionally break the data validation cells via copy-paste, I can simply have them recreated at the end of the script, which would at least allow the next user to have a working form.

However, I am not familiar with the VBA syntax used to (re)create a data validation cell.

For example, I'd need VBA to create in cell G6 a dropdown list:

1. With the "Ignore Blank" unchecked.
2. With the source being a named ranged called ProductService.
3. (If it matters) With the source being on a sheet named "Admin". VBA name within the editor is "Sheet2"

Is this something I can ask for help with in this thread or is it better to create a new one specifically about it?

And thanks again to all for the assistance!

 

[jkpieterse]

The macro recorder should give you a good idea