Initialize an Excel Form When It Opens
On many occasions, a developer needs to initialize an Excel form when it opens. Such tasks as populating a list or combo box are common requirements during the form initialization process. If you include a UserForm_Initialize subroutine in the form, it will be triggered when the form is opened.
For the example below:
(1) The user clicks a button on the main Excel worksheet which says "Open Form"
(2) The Procedure associated with the button issues the MyFormName.Show command.
(3) When the form starts to open, the "UserForm_Initialize" event is triggered
(4) The code below uses the event to populate a list box.
Program Code
Option Explicit
Option Base 1
' *************************************
' This is the form initialize event
' *************************************
Private Sub UserForm_Initialize()
With ListBox1
.Clear
.AddItem "Joe"
.AddItem "Mary"
.AddItem "Jim"
.AddItem "Art"
.AddItem "Susan"
.AddItem "Lisa"
End With
End Sub
' **************************************
' Other Miscellaneous Form Object Events
' **************************************
Private Sub TextBox1_Change()
Dim x As Long, z
ListBox1.ListIndex = -1
For x = 0 To ListBox1.ListCount - 1
ListBox1.ListIndex = x
z = ListBox1.Text
If LCase(Left(ListBox1.Text, Len(TextBox1.Text))) = _
LCase(TextBox1.Text) Then Exit Sub
Next x
End Sub
Private Sub TextBox1_KeyDown(ByVal KeyCode As _
MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then TextBox1.Text = ListBox1.Text
End Sub