Userform with Excel 2018 for MacOS

Question

I saw that functionality of adding userform into Excel 2018 for MacOS (or since Excel 2016) was not possible unlike with Excel 2011.

When I say "adding userform", I am talking about the "UI" designer which allows to design the buttons, boxes, lists. (Actually it seems that adding userform is only available on Windows version of Excel 2018.)

I am seeking to build a simple userform with Excel 2018 for MacOS.

If the "UI" designer is not available, can I directly code the userform with only a VBA code source (can the design be directly coded)?

Shornick

Can't confirm (don't have a Mac), but I'm pretty sure the latest VBE on Mac is pretty much on par with the VBE on Windows, functionality-wise - it can't be extended with add-ins, but I'm pretty sure there is a MSForms designer. Did you try adding a new form to your VBA project? – Pignut

Also, yes, you can make a userform with just VBA code. It's most likely tedious and painful and you'll absolutely want to implement some not-quite-beginner-friendly OOP principles lest you want a maintenance nightmare, but entirely feasible. – Pignut

Anyway please take the tour and read How to Ask - asking for off-site tutorials/links is explicitly off-topic on this site. – Pignut

The VBE on Mac is nearly up to parity with Windows. However, there is still no UserForm designer capability. You can see UserForms in the Project Explorer, and you can edit the code, but you can't see the form itself. A few users have reported seeing a Mac VBE Designer, but apparently only a rare few have had it turned on, perhaps by mistake, and everyone else is still waiting. – Outfield

Answer 1

A screenshot of a programmatically generated UserForm object in Excel for Mac - Microsoft 365

The userform object had to be generated by invoking the Add() method on the VBComponents collection of the VBProject associated with the ThisWorkbook object, as follows:

Set objForm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)

This created a userform object named UserForm1. I briefly saw the visual editor and was able to drag and drop a label control and a command button,for the newly created form but subsequent attempts failed.

So I added code to the UserForm_Initialize() event procedure, manually positioning and configuring the existing controls. I also added code to the automatically generated CommandButton1_Click() event procedure stub.

Option Explicit

Private Sub CommandButton1_Click()
   MsgBox prompt:="Bye for now!"
   Unload Me
End Sub


Private Sub UserForm_Initialize()

Me.Height = 200
Me.Width = 500
Me.Caption = "UserForm On MacOS!!"


With Me.CommandButton1
   .Top = 10
   .Left = 400
   .Width = 50
   .Height = 30
   .Caption = "OK!"
   .Font.Size = 20
   .Font.Bold = True
End With

With Me.Label1
   .Caption = "Hallelujer!"
   .Width = 120
   .Height = 30
   .Left = 5
   .Top = 10
   .BorderStyle = fmBorderStyleSingle
   .SpecialEffect = fmSpecialEffectEtched

   With .Font
      .Name = "Arial"
      .Size = 20
      .Bold = True
   End With
End With

End Sub

The form is invoked via a macro attached to a custom button on the Ribbon.

Public Sub MakeForm()

Dim objForm As UserForm

'Execute the following statement once for each userform object to be created
'and then disable it
'Set objForm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)

'Display the userform
UserForm1.Show


End Sub

This seems to demonstrate that it is possible to insert a UserForm control into a VBProject.

It also suggests that the underpinnings of UserForm support do indeed exist in Excel for Mac but they are as of yet not fully implemented.

Hypothec

Answer 2

So I asked this on a different thread but has anyone figured out the code to get the userform to pop back up to edit?

The setobjForm code opens a designer which works perfectly.

There has to be some code hack to trigger the form designer and toolbox to open again for editing an existing form.

Shiah

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Acquiesce

If you have a new question, please ask it by clicking the Ask Question button. Include a link to this question if it helps provide context. - From Review – Inflight

Answer 3

This VBA Code will reliably display the UserForm it adds and using View > Toolbox the toolbox is opened allowing the UI to be used to design a UserForm.

Option Explicit

Private Sub subCreateuserform()

' https://www.mrexcel.com/board/threads/creating-a-userform-programmatically-error.1248015/

    Dim MyUserForm As VBComponent
    Dim strName As String

    ActiveWorkbook.Save
    
    Call subDeleteForm("frmMyForm")
    
    strName = "frmMyForm"

    Set MyUserForm = ActiveWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
        
    With MyUserForm
        .Properties("Height") = 377
        .Properties("Width") = 260
        .Name = strName
        .Properties("Caption") = "My Form"
    End With
    
End Sub

Public Sub subDeleteForm(strUserform As String)

Dim VBComps As VBIDE.VBComponents

Dim VBComp As VBIDE.VBComponent

    Set VBComps = ActiveWorkbook.VBProject.VBComponents
    
    On Error Resume Next
        Set VBComp = VBComps(strUserform)
        VBComps.Remove VBComp
    On Error GoTo 0
    
    Set VBComps = Nothing
    
End Sub

I have tested this on MacOS 14.0 Sonoma / Microsoft Excel for Mac Version 16.78.3 (23102801) Microsoft 365 Subscription / Visual Basic for Applications 7.1 (2012).

The references required are -

Visual Basic for Applications

Miscosoft Excel 16.0 Object Library

Microsoft Forms 2.0

Microsoft Office 16.0 Object Library

Microsoft Visual Basic for Applications Extensibility 5.3

You need to trust the VBA object model which can be switched on using Excel > Preferences > Security

and tick

Enable all macros

Trust access to the VBA project object model

Talos