Resize UserForm and controls

When you open a workbook with a UserForm that you create in Excel for Windows you will notice that you think you're getting old because the UserForm will popup very small and you need an optical device. You can copy the code below in the UserForm module and a normal module of your workbook to change the size automatic when you open the UserForm on a Mac. Change the 1.333333 in the code if you think it is too big or too small.

Copy the code below in your UserForm module :

Private Sub UserForm_Initialize()
#If Mac Then
ResizeUserForm Me
#End If
End Sub

Copy the code below in a normal module :

Option Explicit
Public Const gUserFormResizeFactor As Double = 1.333333

Function ResizeUserForm(frm As Object, Optional dResizeFactor As Double = 0#)
'Created by Jon Peltier
Dim ctrl As Control
Dim sColWidths As String
Dim vColWidths As Variant
Dim iCol As Long

If dResizeFactor = 0 Then dResizeFactor = gUserFormResizeFactor
With frm
'.Resize = True
.Height = .Height * dResizeFactor
.Width = .Width * dResizeFactor

For Each ctrl In frm.Controls
With ctrl
.Height = .Height * dResizeFactor
.Width = .Width * dResizeFactor
.Left = .Left * dResizeFactor
.Top = .Top * dResizeFactor
On Error Resume Next
.Font.Size = .Font.Size * dResizeFactor
On Error GoTo 0

' multi column listboxes, comboboxes
Select Case TypeName(ctrl)
Case "ListBox", "ComboBox"
If ctrl.ColumnCount > 1 Then
sColWidths = ctrl.ColumnWidths
vColWidths = Split(sColWidths, ";")
For iCol = LBound(vColWidths) To UBound(vColWidths)
vColWidths(iCol) = Val(vColWidths(iCol)) * dResizeFactor
Next
sColWidths = Join(vColWidths, ";")
ctrl.ColumnWidths = sColWidths
End If
End Select
End With
Next
End With
End Function

If you have copy the code in the correct location you will see that if you open the Userform in Mac Excel the UserForm and controls are bigger. If you open the UserForm in Excel for Windows the code will not run because we test the OS with the conditional compiler constant #If Mac

11-May-2021
Web design by Will Woodgate