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
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,775
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
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?
 

Forum statistics

Threads
1,081,530
Messages
5,359,347
Members
400,524
Latest member
Excelbat

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top