VBA Allow ListBox creation while sheet protected

Question

I have a spreadsheet which uses code to add multi-select ListBoxes containing data from a named range. Selections from this list are then output to the selected cell.

Only when I protect the sheet to I get a runtime error 1004: unable to get the add property of the listboxes class and therefore the ListBoxes cannot be added.

The selectedCell however is unlocked.

How can I prevent deletion of data and formulas while allowing ListBoxes to be created?

Part of the code is as follows:

Public Sub CreateCatPopUp(ByRef selectedCell As Range)
    Set catSelectCell = selectedCell
    Dim CatPopUpCell As Range
    Set CatPopUpCell = catSelectCell.Offset(1, 0)
    Const CAT_POPUP_HEIGHT As Double = 300
    Dim catBox As ListBox
    Set catBox = ActiveSheet.ListBoxes.Add(CatPopUpCell.Left, _
                                              CatPopUpCell.Top, _
                                              ActiveCell.Width, _
                                              CAT_POPUP_HEIGHT)

Have you tried temporarily unlocking the sheet just for the Listboxes.Add part? You should be able to use worksheet.Unprotect(password) / .Protect(password) for that. — Slaqr

aye cee aye cee · Accepted Answer · 2021-07-17T14:25:48

If you set UserInterFaceOnly to true it will protect the user interface and allow macros.

I added the below code to the Workbook_Open event and now all vba on the sheet is working.

ActiveSheet.Protect UserInterFaceOnly:=True

VBA Allow ListBox creation while sheet protected

1 Answers