UserForm Mac OS - VBA Programming

gerefontes · ‎Jul 29 2023

Is there a way to programming UserForms in VBA - Excel for Mac OS

I'm using Microsoft Excel for Mac - Version 16.74
First o all you need to check VBAProject References :

[X] Microsoft Forms 2.0 Object Library
[X] Microsoft Visual Basic for Applications Extensibility 5.3

a simple example:

Sub Form()
    Set objForm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
End Sub

This code creates a UserForm that you can edit but there is a problem, once created the form appears with toolbox, but when you close that window ex: UserForm1(UserForm) you will never see the toolbox again, after that you can only access code window ex: UserForm1(Code) and no more UserForm Design window. That said, you can do everything programmatically.

I will give a sample of a UserForm with a CommandButton that will show a MsgBox and then close the Form after MsgBox were clicked.

This code is to be at Module1 or so

Sub TestForm()
    Dim TempForm As Object
    Dim NewButton As MSForms.CommandButton
    
    Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3) 'vbext_ct_MSForm
    With TempForm
        .Properties("Caption") = "Form Name"
        .Properties("Width") = 400
        .Properties("Height") = 250
    End With

    Set NewButton = TempForm.Designer.Controls.Add("forms.CommandButton.1")
    With NewButton
        .Name = "Botao1"
        .Caption = "Click Me"
        .BackColor = vbBlue
        .Font.Size = 14
        .Font.Bold = True
        .Left = 60
        .Top = 100
        .MousePointer = fmMousePointerSizeAll
        .ControlTipText = "Click to show a MsgBox"
    End With
    
    ' *******************************************
    ' **********|     UserForm Code      |*********
    ' *******************************************
    With TempForm.CodeModule
        .InsertLines .CountOfLines + 1, "Sub Botao1_Click()"
        .InsertLines .CountOfLines + 1, "    MsgBox ""Hello!"""
        .InsertLines .CountOfLines + 1, "    Unload Me"
        .InsertLines .CountOfLines + 1, "End Sub"
        .InsertLines .CountOfLines + 1, ""
        .InsertLines .CountOfLines + 1, "Sub Botao1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)"
        .InsertLines .CountOfLines + 1, "    Botao1.ForeColor = vbRed"
        .InsertLines .CountOfLines + 1, "End Sub"
        .InsertLines .CountOfLines + 1, ""
        .InsertLines .CountOfLines + 1, "Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)"
        .InsertLines .CountOfLines + 1, "    Botao1.ForeColor = vbBlack"
        .InsertLines .CountOfLines + 1, "End Sub"
        .InsertLines .CountOfLines + 1, ""
    End With
    ' *******************************************
    ' **********|     UserForm Code      |*********
    ' *******************************************

    VBA.UserForms.Add(TempForm.Name).Show
    ThisWorkbook.VBProject.VBComponents.Remove TempForm
End Sub

I think this is a good start point.

Listed bellow some Controls and Events:

Sub Button1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)

Sub Button1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)

Sub Button1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)

Sub Button1_Click()

Sub CheckBox1_Change()

*************************************

Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3) 'vbext_ct_MSForm

Set CheckBox1 = TempForm.Designer.Controls.Add("forms.CheckBox.1")

Set OptButton1 = TempForm.Designer.Controls.Add("forms.OptionButton.1")

Set OptButton2 = TempForm.Designer.Controls.Add("forms.OptionButton.1")