The Excel VBA Initialize Form Event

Ongoing tutorial - First part is here: Part One


When an Excel form launches, a series of events occur. One of these events is called Initialize. The Initialize event happens before any of the form and its controls are drawn to the screen. This allows you to set up initialization code, such as setting variables, switching off or on any controls, and a whole lot more besides. What we'll do is to switch off some buttons so they can't be clicked. We'll also add some data to the ComboBox on our form.


To get at the Initialize event, right click the name of your form in the Project Explorer on the left of the Excel VBA Editor. (If you can't see the Project Explorer, click View > Project Explorer from the menu at the top of the VBA Editor.) From the menu that appears, select View Code:

The View Code menu in the VBA Editor

When you click on View Code, you should see a coding window open. At the top, you'll see two dropdown boxes, one for General and one for Declarations. Click the General dropdown box and select UserForm:

The General dropdown box

Now click the Declarations dropdown box and select the Initialize event:

The Initialize event in the Declarations dropdown box

You should then see a code stub appear for UserForm_Initialize.

Private Sub UserForm_Initialize( )

End Sub

The first thing we can do is to switch off the Back, Next and Save buttons. The Back and Next buttons are on our View Photo tab. We don't want these to work until the Load Image Information button is clicked. Likewise, we don't want the Save button on the Add New Photo tab to work until a new image has been selected.

To switch a button off you can set its Enabled property to False. To switch it back on, you set it to True. So add the following three lines to your Initialize event:

cmdBack.Enabled = False
cmdNext.Enabled = False
cmdSave.Enabled = False

We can also add items to the ComboBox from the Initialize event. To add an item to a ComboBox you need the AddItem method. Like this:

ComboBox1.AddItem "Samsung GT-I9100"

After the AddItem method, you need a space. After the space you type whatever text you want for that item. The text goes in double quotes.

Add these lines to your Initialize event:

ComboBox1.AddItem "Samsung GT-I9100"
ComboBox1.AddItem "iPhone"
ComboBox1.AddItem "Canon Ixus"
ComboBox1.AddItem"FujiFilm FinePix"
ComboBox1.AddItem "Canon EOS"

Your coding window should then look like this (we've added some comments):

VBA code for the Initialize event

Try it out. Click anywhere inside of your Initialize event. Now press F5 on your keyboard to launch your form. The View Photo tab should look like this:

VBA Form showing results of the Initialize event

Notice that the two buttons at the bottom are switched off, so that they can't be clicked.

Select your Add New Photo tab. Click on your ComboBox to see the list of items you added:

Excel VBA Form showing combo box items


Return to the Excel VBA Editor and we'll write the code for the Load Image Information button. We'll start that in the next lesson below.