Forum Discussion
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/office-mac/idmsoexcelmac
http://www.java2s.com/Code/VBA-Excel-Access-Word/Forms/CreatingUserFormsProgrammatically.htm
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
Hope this helps someone
Gere Fontes
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 SubSub Workbook_BeforePrint(Cancel As Boolean)
If Saved = False ThenEnd 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?- I use: Sonoma and Excel version 16.78
