Compile Error argument not optional on Mac only

Question

I have a working Excel sheet in Excel 2010 on Windows 10 which is failing on my client's side on Excel 2011 for Mac because of the "argument not optional" error when they click on the button. Additionally, it isn't working on their end on a different computer running Office 365. It seems to be highlighting Characters in the 6th line of code:

Sub Rectangle4_Click()

Dim xSelShp As Shape, xSelLst As Variant, I As Integer
Set xSelShp = ActiveSheet.Shapes(Application.Caller)
Set xLstBox = ActiveSheet.ListBox4

If xLstBox.Visible = False Then
    xLstBox.Visible = True
    xSelShp.TextFrame2.TextRange.Characters.Text = "SAVE"
Else
    xLstBox.Visible = False
    xSelShp.TextFrame2.TextRange.Characters.Text = "SELECT"
    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("R4") = Mid(xSelLst, 1, Len(xSelLst) - 1)
    Else
        Range("R4") = ""
    End If
End If
End Sub

The ListBox it is showing/hiding is an ActiveX ListBox.

I doubt it's relevant, but there are 40 of these Rectangle buttons in the sheet, all with near identical code, just switching 4 references.

Can anyone see what I'm missing here? Or know anything about cross-platform issues with code like this?

Thank you.

I don't thin ActiveX controls work on Mac, though I don't have one to test. — Tim Williams
That would explain a lot! But I would think it would work for Office 365, right? I don't have either to test myself, hence why I'm checking here. — Brian - RGY Studio
Also, does anyone have advice on whether it would be better to switch to form controls? I am really lost given that it's failing in 365 on a PC as well as on the Mac... — Brian - RGY Studio

Brian - RGY Studio Brian - RGY Studio · Accepted Answer · 2020-05-06T18:42:12

In case anyone comes across this, it does seem to be that Tim was correct and ActiveX controls do not work on a Mac. But they also did not work in Office 365. I switched this to form controls and got everything working perfectly on my client's side. My advice to my past self is to work with form controls from the beginning and avoid ActiveX at all costs if you are working on something you are sending to someone else.

Compile Error argument not optional on Mac only

1 Answers