Runtime error -214702024809 (80070057) only after protecting sheet

[Blasirl]

I am in the process of trying to create an input spreadsheet that will eventually be used to output a form for each user on a forum. For the most part I used dropdowns to allow for most selections. My issue at the moment is when I protect the worksheets, I get a runtime error -214702024809 (80070057), but if I unprotect it, it works fine (up to the limits of my Excel skills so far).

I think it has something to do with the ListBox ActiveX Control I am using. I tried to unprotect the button I created a number of different ways but none seemed to work. I am using (and trying to understand) some borrowed VBA code to get the dropdown of list boxes to select from. It is attributed within the code. I am not a VBA programmer at all, but will learn bits and pieces if need be.

Any help would be appreciated.

The version of Excel I am using is pictured below.

The attached file is a macro-enabled Excel file that has been sheet protected. The password is: zx

Windows Build/VersionVersion 21H2 (OS Build 22000.1696) as of 27MAR2023

Sub Rectangle1_Click()
'Updated by Extendoffice 20200730
Dim xSelShp As Shape, xSelLst As Variant, I, J As Integer
Dim xV As String
Set xSelShp = ActiveSheet.Shapes(Application.Caller)
Set xLstBox = ActiveSheet.ListBox1
If xLstBox.Visible = False Then
    xLstBox.Visible = True
    xSelShp.TextFrame2.TextRange.Characters.Text = "Pickup Options"
    xStr = ""
    xStr = Range("ListBoxOutput").Value
    
    If xStr <> "" Then
         xArr = Split(xStr, ";")
    For I = xLstBox.ListCount - 1 To 0 Step -1
        xV = xLstBox.List(I)
        For J = 0 To UBound(xArr)
            If xArr(J) = xV Then
              xLstBox.Selected(I) = True
              Exit For
            End If
        Next
    Next I
    End If
Else
    xLstBox.Visible = False
    xSelShp.TextFrame2.TextRange.Characters.Text = "Select Options"
    For I = xLstBox.ListCount - 1 To 0 Step -1
        If xLstBox.Selected(I) = True Then
        xSelLst = xLstBox.List(I) & ";" & xSelLst
        End If
    Next I
    If xSelLst <> "" Then
        Range("ListBoxOutput") = Mid(xSelLst, 1, Len(xSelLst) - 1)
    Else
        Range("ListBoxOutput") = ""
    End If
End If
End Sub

I have tried in vain to use xl2bb and so far am not able to.

This is what it looks like after i hit end

The spreadsheet is posted here if you wish to look at it. https://www.elevenforum.com/t/only-...ed-i-get-a-runtime-error-what-is-wrong.13736/

 

[davesexcel]

I haven't tested out your code but have you tried unlocking the control? Go to the controls property and unlock it. Possibly the controls will work when the sheets are protected.

Or just keep the sheets unprotected.

 

[Blasirl]

I will give that a try. Can you explain the process for that?
As for leaving them unprotected, this will eventually be a public page and since I do not know the final "face" of the page, if it stays like this, it would be to easy to inadvertently mess it up.

 

[Blasirl]

This is what I see when I follow Microsoft's instructions:

Per what they say to do, my only choice to unlock the controls is to unprotect the spreadsheet which in turn defeats my goal.

 

[Blasirl]

Solved by completely rebuilding the sheet