Contents
In VBA, the Resize event is typically used within UserForms to handle changes in size. This event occurs when a UserForm is resized, either through user interaction or through code. To use the Resize event, you need to write a subroutine in the code module for the UserForm that is triggered when the form is resized.
Here’s how you can add and use the Resize event in a UserForm
- Open the VBA editor by pressing ALT + F11 in Excel.
- In the Project Explorer, find the UserForm you want to work with. If you don’t have a UserForm, you can insert one by right-clicking on any of the VBA project items, selecting Insert, and then UserForm.
- Once you have your UserForm, double-click on it to open its code window.
- In the code window, select UserForm from the left drop-down list (Object Box) to ensure you are editing the correct object.
- From the right drop-down list (Procedure Box), select Resize. The VBA editor will automatically create an empty UserForm_Resize subroutine for you.
- Inside the UserForm_Resize subroutine, you can write the code you want to execute whenever the form is resized.
Example
Private Sub UserForm_Resize()
' Example code to dynamically adjust a control's size and position with the form
If Me.InsideWidth > 0 And Me.InsideHeight > 0 Then ' Avoid errors during form initialization
' Assuming there is a TextBox named txtExample on the form
Me.txtExample.Left = 10
Me.txtExample.Top = 10
Me.txtExample.Width = Me.InsideWidth 20
Me.txtExample.Height = Me.InsideHeight 20
End If
End Sub
In this example, whenever the UserForm is resized, the text box within it (txtExample) is adjusted so that it maintains a 10-point margin from the form’s edges. The InsideWidth and InsideHeight properties of the UserForm are used to determine the new size of the text box dynamically.
Note that UserForm_Resize gets called once the size has already been changed, so your code should react to the new size of the form.
Remember that if the UserForm is resized to be very small, your controls might overlap or become too small to be functional, so you may want to include some checks to maintain minimum sizes for your controls.
Lastly, if you want to programmatically resize your UserForm, you can set its Height and Width properties in code, which will also trigger the Resize event.
' Setting the UserForm's size programmatically
UserForm1.Height = 300
UserForm1.Width = 400
Always make sure to test the resizing behavior at runtime to ensure it works as expected.
Unlock Your Potential
Excel
Access
Power BI
