Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

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 your getting old because the userform will popup very small and you need a optical device.
You can copy the code below in the Userform module of your workbook to change the size automatic when you open the userform on a Mac. Change the 1.5 (50 % larger) in the code if you think it is big or to small.

Private Sub UserForm_Initialize()
    Call AdjustSizeForMac
End Sub

Sub AdjustSizeForMac()
    Dim ControlOnForm As Object

    #If Win32 Or Win64 Then
        'No mac so not run the code
        Exit Sub
    #End If

    'Change size coefficient
    Const SizeCoefForMac = 1.5

    With Me
        'Change Userform size
        .Width = .Width * SizeCoefForMac
        .Height = .Height * SizeCoefForMac

        'Change controls/font on the userform
        For Each ControlOnForm In .Controls
            With ControlOnForm
                .Top = .Top * SizeCoefForMac
                .Left = .Left * SizeCoefForMac
                .Width = .Width * SizeCoefForMac
                .Height = .Height * SizeCoefForMac
                On Error Resume Next
                .Font.Size = .Font.Size * SizeCoefForMac
                On Error GoTo 0
            End With
        Next
    End With
End Sub

But what if you want to change the size of a userform/controls and save this settings in the workbook.
You not need the code above to run each time then when you open the userform.
Note: the macro is working in Windows and on the Mac

You can change the properties of the form and all of the controls by hand, no problem if you have a few of them. But not so funny if there are 100 controls or so.

You can use the macro below to do it for you

1: Open the workbook with the userform that you want to change
2: Open the VBA Editor
3: Insert the macro below in a module of the workbook
4: Change this two lines in the code

'You can change the 1.3, use lower then 1 to make it smaller.
Const SizeCoefficient = 1.3

'Enter the name of the UserForm
NameUserform = "Userform1"

5: Run the macro
6: delete the macro in the module
7. Check out if you like the size now of the UserFom and Controls
8: Save Your workbook if you like it, close without saving if you not like it

If correct the size if your UserForm and Controls are changed now (font size also)

Sub ChangeUserFormAndControlsSize()
    Dim MyUserform As Object
    Dim ControlOnForm As Object
    Dim NameUserform As String

    'You can change the 1.3, use lower then 1 to make it smaller
    Const SizeCoefficient = 1.3

    'Enter the name of the UserForm
    NameUserform = "Userform1"

    Set MyUserform = ThisWorkbook.VBProject.VBComponents(NameUserform)
    With MyUserform
        .Properties("Height") = .Properties("Height") * SizeCoefficient
        .Properties("Width") = .Properties("Width") * SizeCoefficient
    End With

    For Each ControlOnForm In MyUserform.Designer.Controls
        With ControlOnForm
            .Top = .Top * SizeCoefficient
            .Left = .Left * SizeCoefficient
            .Width = .Width * SizeCoefficient
            .Height = .Height * SizeCoefficient
            On Error Resume Next
            .Font.Size = .Font.Size * SizeCoefficient
            On Error GoTo 0
        End With
    Next ControlOnForm

End Sub

Note: In Excel for Windows 2002-2010 you must have checked this setting:
Trust access to Visual Basic Project (object model)
This is only for the macro above and not for the first example that change the size at runtime.

Office 2003 and Office XP
1.Open the Office 2003 or Office XP application.
2:On the Tools menu, click Macro, and then click Security to open the Macro Security dialog box.
3.On the Trusted Sources tab, click to select the Trust access to Visual Basic Project check box
4.Click OK to apply the setting.

Office 2007
1.Open the 2007 Microsoft Office system application.
2.Click the Microsoft Office button, and then click Application Options.
3.Click the Trust Center tab, and then click Trust Center Settings.
4.Click the Macro Settings tab, click to select the Trust access to the VBA project object model check box
5.Click OK.
6.Click OK.

Office 2010 and 2013
1.Open the 2010 Microsoft Office system application.
2.Click on the File menu, and then click Application Options.
3.Click the Trust Center tab, and then click Trust Center Settings.
4.Click the Macro Settings tab, click to select the Trust access to the VBA project object model check box
5.Click OK.
6.Click OK.