I made a UserForm for Windows. How do I go about making it work for Mac?

[Magic Polygon]

I built a UserForm on Windows, and for Windows, but with the intention of it ultimately working for Mac. I can't find many resources on developing UserForms for Mac, so I'm hoping I could get some suggestions as to how I could build a working Mac version. Here is a link to my Worksheet: Userform.xlsm

 

[Dan_W]

Earlier this year, I had to use VBA on a Mac for several months, and I found the experience painful. It isn't easy, and a lot of the functionality that should be par-for-the-course is not available with Mac. I am disgusted with how poorly Microsoft treats Mac users.

Anyway, in answer to your question - Userforms is one of those fundamental things that went 'missing' for Mac users from around version 2016, I think... You can make userforms in workbooks in Windows, and open them in Mac, but you can't build them the same way in Mac. A lot of the controls available in Windows aren't available in Mac - so you are, for the most part, limited to the basic control set (buttons, option buttons, labels, etc). You need to build them all with code, though. Here is a good overview and tutorial on how to do it by someone I know over on Reddit:

https://www.reddit.com/r/vba/comments/11uqkua

In short, you need to to add the reference: Microsoft Visual Basic for Applications Extensibility 5.3

And then use the following code to build the Userform (with some sample controls). Full credit to ITFuture for the code:

Public Function CreateForm1()

'MAKE SURE YOU ADD THE FOLLOWING REFERENCES ON THE MAC
'       Microsoft Visual Basic for Applications Extensibility 5.3
'       vbapp type library

Dim form1Name As String: form1Name = "testForm1"
Dim form1 As VBComponent
Dim lbl1 As MSForms.Label
Dim formExists As Boolean

Dim i As Long
For i = 1 To ThisWorkbook.VBProject.VBComponents.Count
    With ThisWorkbook.VBProject.VBComponents(i)
        If .Type = vbext_ct_MSForm Then
            If .Name = form1Name Then
                formExists = True
                Exit For
            End If
        End If
    End With
Next

If Not formExists Then
    Set form1 = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
    With form1
        .Properties("Height") = 150
        .Properties("Width") = 200
        On Error Resume Next
        .Name = form1Name
        .Properties("Caption") = "Dynamic Label Form"
    
        Dim btn As MSForms.CommandButton
        Set btn = .Designer.Controls.Add("forms.CommandButton.1")
        With btn
            .Caption = "Cancel"
            .Height = 18
            .Width = 44
            .Left = CLng(form1.Properties("Width") / 2) - CLng(btn.Width)
            .Top = 5
        End With
        Set btn = .Designer.Controls.Add("forms.CommandButton.1")
        With btn
            .Caption = "OK"
            .Height = 18
            .Width = 44
            .Left = CLng(form1.Properties("Width") / 2) + 1
            .Top = 5
        End With
        
        Set lbl1 = .Designer.Controls.Add("Forms.Label.1")
        With lbl1
            .Caption = "I'm a Label"
            .AutoSize = True
        End With
    
        On Error Resume Next
        With .CodeModule
            Dim X As Long
            X = .CountOfLines
            .InsertLines X + 1, "Sub CommandButton1_Click()"
            .InsertLines X + 2, "    Unload Me"
            .InsertLines X + 3, "End Sub"
            .InsertLines X + 4, ""
            .InsertLines X + 5, "Sub CommandButton2_Click()"
            .InsertLines X + 6, "    Unload Me"
            .InsertLines X + 7, "End Sub"
        End With
    End With
End If

End Function