Resize Controls | Mac Excel Automation

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 the second code block into 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 :

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