Excel - selecting from dropdown in protected worksheet

Question

I currently have a worksheet that I need to protect cells but allow for the user to use the dropdown selections in a few different ranges. The ranges have a variable column number since more columns can be added.

When I manually protect a worksheet and select the appropriate options, all cells are locked but the user can still select from the dropdowns in the necessary cells. However, when I lock the worksheet using VBA (which is necessary for some of my macros to function), this functionality dissapears.

All I'm doing in VBA is

'variable declarations

ws1.Unprotect Password:="pw"
ws1.Cells.Locked = False

'lots of code here

ws1.Cells.Locked = True
ws1.Protect Password:="pw", UserInterfaceOnly:=True, _
AllowDeletingColumns:=True, AllowFiltering:=True, AllowFormattingCells:=True,_
AllowFormattingColumns:=True, AllowUsingPivotTables:=True

I've read that AllowFiltering:=True is what's supposed to allow for the dropdown selection in locked cells, but that doesn't seem to work here. Anyone know what the difference is between me manually locking and locking through vba?

proposals: Place the functions/data where you need the locked worksheet on another worksheet? Perhaps a Userform and a dropdown there does solve your issue? — simple-solution
Your VBA is locking all cells on the sheet, including the ones you'd like to be editable. Why do that? — Tim Williams
Is it not necessary to lock them again after unlocking them? Or is unprotecting the worksheet enough for the code in the middle to do things like hide rows/add rows & columns/and add formulas? — Valkeif
And I won't be able to split the worksheet, the other columns (locked) columns and rows need to be next to the cells with drop-down selections for ease of use. — Valkeif
@TimWilliams Currently if I drop the ws1.Cells.Locked = True, after executing none of the cells are locked. This is if I manually set the worksheet protection, and then have the code just say ws1.Unprotect Password :="pw" .....code...ws1.Protect Password:="pw" — Valkeif

cording12 cording12 · Accepted Answer · 2019-02-26T16:48:02

You should be able to just specify the cell range that you want to let a user edit:

Range("Enter range here").Locked = False

I'm not sure without being able to test however the below should do the trick:

'variable declarations

ws1.Unprotect Password:="pw"
ws1.Cells.Locked = False

'lots of code here

ws1.Cells.Locked = True
Range("Enter range here").Locked = False 

ws1.Protect Password:="pw", UserInterfaceOnly:=True, _
AllowDeletingColumns:=True, AllowFiltering:=True, AllowFormattingCells:=True,_
AllowFormattingColumns:=True, AllowUsingPivotTables:=True

Excel - selecting from dropdown in protected worksheet

1 Answers