Excel Combo Box Drop Down (form control) non functional when Protected

Question

EDIT: Should've researched further. This solved it: https://excel.tips.net/T002691_Selecting_Combo_Boxes_in_Locked_Worksheets.html

Simply unlock the cell associated with combo box (right click, format cells, Protection, uncheck 'locked)

I replaced data validation with a combo box form control, which was working fine with the worksheet protected. Now with the combo box I get the message advising me of the sheet protection and it won't accept any changes (it works fine unprotected). I protect the sheet with the below code on WS open (I've seen some answers suggesting UserInterfaceOnly:= true would allow the combo box to function, but that is unfortunately not the case).

Any suggestions?

Private Sub Workbook_Open()
Dim Current As Worksheet
For Each Current In Worksheets

With Current
    .Protect UserInterfaceOnly:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering:=True
End With

Next

Worksheets("Pivot").UnProtect

End Sub

If it's solved, then please include the solution as an answer (you can answer your own question) so others will know this question is answered. (when you do, include a brief explanation and not just the link) — cybernetic.nomad

DanCo89 DanCo89 · Accepted Answer · 2019-09-19T20:02:13

EDIT: Should've researched further. This solved it: https://excel.tips.net/T002691_Selecting_Combo_Boxes_in_Locked_Worksheets.html

Simply unlock the cell associated with combo box (right click, format cells, Protection, uncheck 'locked)

Excel Combo Box Drop Down (form control) non functional when Protected

1 Answers