Dropdown box protection

[bloesch1]

Hi, I have a protected worksheet that has a number of macros that work correctly which are similar in that they unprotect the sheet at the beginning and protects it when the macro has finished. However, I have a dropdown box that I'm attempting to apply the same logic to which isn't working correctly.

When I select a value from the dropdown list, I receive an error stating that the cell or chart I'm trying to change in protected and therefore read-only. Once I hit ok, the feature works fine and I'm able to select a value from the list and get the correct output. From the code below, can you see why I'm still receiving the initial error?

Thank you!

Sub DropDown133_Change()
Sheets("MainMap").Unprotect Password:="password"

Dim strMap As Integer

strMap = Range("I4").Value
Select Case strMap
Case 1
1
Case 2
2
Case 3
3
Case 4
4
Sheets("MainMap").protect Password:="password"
End Select


End Sub

 

[mikerickson]

I don't think it is the code. Is the control linked to a cell that is locked when protected? If so changing the protection formatting for that cell might fix things.

 

[Demosthenes&Locke]

Is the sheet or form this entry is on protected? Try putting the code in a module and having the dropdown change call the code.

 

[bloesch1]

Thanks for the quick response!

I've double-checked all the cells and objects and I set them all the unlocked for experimental purposes. However, I still get the initial error...

 

[Demosthenes&Locke]

ok, you can fix it I believe by turning notifications off.

Sub DropDown133_Change()
Application.DisplayAlerts = False
Sheets("MainMap").Unprotect Password:="password"
 
Dim strMap As Integer
 
strMap = Range("I4").Value
Select Case strMap
Case 1
1
Case 2
2
Case 3
3
Case 4
4
Sheets("MainMap").protect Password:="password"
End Select
Application.DisplayAlerts = True
 
End Sub

<!-- / message -->

 

[bloesch1]

That led me to a Run-time error '438'
Object doesn't support this property or method.

 

[Demosthenes&Locke]

Are you using the revised code above?

 

[bloesch1]

Yes, I followed your instructions verbatim

 

[bloesch1]

One error I've made in my description of the situation is that it only seems to work (taking into account I have to hit "ok" when the error initially appears) is when the final code that adds the protection back IS NOT in the code. Sorry for the omission.

 

[Demosthenes&Locke]

Is this in a form?
I suggest moving the actual changing code to a module:
Leave this in your page/form/wherever.

Sub DropDown133_Change()
Application.DisplayAlerts = False
InterpretChange()
Application.DisplayAlerts = True
End Sub

Put this in the module.

Sub InterpretChange
Sheets("MainMap").Unprotect Password:="password"
 
Dim strMap As Integer
 
strMap = Range("I4").Value
Select Case strMap
Case 1
1
Case 2
2
Case 3
3
Case 4
4
End Select
Sheets("MainMap").protect Password:="password"
 
End Sub

Also, can you tell me more about where you have your code? Where is this dropdown? Is it a result of data validation or is it a button?