UserForm Mac OS - VBA Programming

[gerefontes]

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-micro...

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

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-microso...

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-microso...

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-con...

Hope this helps someone

Gere Fontes

[#2]

@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?

[#3]

I use: Sonoma and Excel version 16.78