Forum Discussion

gerefontes's avatar
gerefontes
Copper Contributor
Jul 30, 2023

UserForm Mac OS - VBA Programming

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")

Set Button1 = TempForm.Designer.Controls.Add("forms.CommandButton.1")

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

Some Useful links

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/add-method-microsoft-forms

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/reference-microsoft-forms

https://learn.microsoft.com/en-us/office/vba/office-mac/idmsoexcelmac

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/examples-microsoft-forms

http://www.java2s.com/Code/VBA-Excel-Access-Word/Forms/CreatingUserFormsProgrammatically.htm

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/concepts-microsoft-forms

https://learn.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.button?view=excel-pia

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/color-constants

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/system-color-constants

 

Hope this helps someone

 

Gere Fontes

 

  • gerefontes 

    Hello! Thank you for your efforts in writing this code. I started working by studying your code. But I can’t figure out how to make it so that in the userform, when the .Caption = “Click Me” button is pressed, the current sheet is automatically saved in a separate book and this sheet is printed.

    On Windows I created the code:
    Private Sub cmdSave_Click()
    If TextBox1.Text = "" Then
    MsgBox("Cannot Empty!")
    Else
    Range("e5").Value = TextBox1.Text
    End If
    If TextBox2.Text = "" Then
    MsgBox("Cannot Empty!")
    Else
    Range("c6").Value = TextBox2.Text
    End If
    If TextBox3.Text = "" Then
    MsgBox("Cannot Empty!")
    Else
    Range("e6").Value = TextBox3.Text
    End If
    If TextBox4.Text = "" Then
    MsgBox("Cannot Empty!")
    Else
    Range("c9").Value = TextBox4.Text
    End If
    If TextBox5.Text = "" Then
    MsgBox("Cannot Empty!")
    Else
    Range("d9").Value = TextBox5.Text
    End If
    If TextBox6.Text = "" Then
    MsgBox("Cannot Empty!")
    Else
    Range("a18").Value = TextBox6.Text
    End If
    Unload Me
    End Sub

    Sub Workbook_BeforePrint(Cancel As Boolean)
    If Saved = False Then

    End If

    End Sub

    Option Explicit

    Sub Make_PDF()
    Dim pdfName As String

    pdfName = Range("A9").Text & Format$(Date, " mm-dd-yyyy") & ".pdf"
    ActiveSheet.ExportAsFixedFormat_
    Type:=xlTypePDF, _
    FileName:=pdfName, _
    Quality:=xlQualityMinimum, _
    IncludeDocProperties:=False, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
    End Sub


    Please tell me what needs to be changed in your code example to get the same result in Excel for Mac?

Resources