[SOLVED] Userform with Excel 2018 for MacOS

Userform with Excel 2018 for MacOS

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

Solution

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.