Dropdown box protection

bloesch1

New Member
Joined
Aug 24, 2010
Messages
25
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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,846
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

Board Regular
Joined
May 11, 2010
Messages
93
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

New Member
Joined
Aug 24, 2010
Messages
25
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

Board Regular
Joined
May 11, 2010
Messages
93

ADVERTISEMENT

ok, you can fix it I believe by turning notifications off.
Rich (BB code):
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 -->
 
Last edited:

bloesch1

New Member
Joined
Aug 24, 2010
Messages
25
That led me to a Run-time error '438'
Object doesn't support this property or method.
 

bloesch1

New Member
Joined
Aug 24, 2010
Messages
25
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

Board Regular
Joined
May 11, 2010
Messages
93
Is this in a form?
I suggest moving the actual changing code to a module:
Leave this in your page/form/wherever.
Rich (BB code):
Sub DropDown133_Change()
Application.DisplayAlerts = False
InterpretChange()
Application.DisplayAlerts = True
End Sub
Put this in the module.
Rich (BB code):
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,873
Messages
5,574,754
Members
412,617
Latest member
mlharris
Top