UserForm Level Events

These events would be triggered based on the actions in the ‘UserForm’.

Examples of Userform events

1. Initializing a user Form
2. Clicking a button in the user Form

1) UserForm Activate Event

This event is triggered when the form is activated, normally when it is displayed.  This event can be used to set up default values e.g. a default company name in the company name text box

Private Sub UserForm_Activate()
TextBox1.Text = “My Company Name”
End Sub

2) Change Event

Most of the controls on the form have a change event, but in this example, the company name text box can use the event to put a restriction on the length of the company name being entered

Private Sub TextBox1_Change ()
If Len (TextBox1.Text) > 20 Then
MsgBox “The name is restricted to 20 characters”, vbCritical
TextBox1.Text = “”
End If
End Sub

3) Click Event

You can use this event to take action from the user clicking on controls on the form, or even the form itself

On this form there is an ‘OK’ button, and having collected a company name, we would want to place it in a cell on the spreadsheet for future reference

Private Sub CommandButton1_Click ()
ActiveSheet.Range(“A1”). Value = TextBox1.Text
Me.Hide
End Sub

This code acts when the user clicks the ‘OK’ button.  It puts the value in the company name input box into cell A1 on the active sheet and then hides the form so that user control is returned back to the worksheet.

4) Command Button Click Event

When you have put a command button onto a spreadsheet, you will want it to take some action. You do this by putting code on the Click event.
You can easily put an ‘Are you sure message?’ on this so that a check is made before your code runs

Private Sub CommandButton1_Click ()
Dim ButtonRet As Variant
ButtonRet = MsgBox(“Are you sure that you want to do this?”, vbQuestion Or vbYesNo)
If ButtonRet = vbNo Then Exit Sub
End Sub

5) Drop Down (Combo box) Change Event

An Active X drop down has a change event, so that if a user selects a particular item from the drop-down list, you can capture their choice using this event and then write code to adapt other parts of the sheet or workbook accordingly.

Private Sub ComboBox1_Change ()
MsgBox “You selected ” & ComboBox1.Text
End Sub

6) Tick Box (Check Box) Click event

You can add a tick or check box to a worksheet so as to provide option choices for the user. You can use the click event on it to see if the user has changed anything on this. The values returned are True or False according to whether it has been ticked or not.

Private Sub CheckBox1_Click ()
MsgBox CheckBox1.Value
End Sub