asked on

How do I add items to a ListBox in Excel VBA when the workbook first opens

Hi,

This relates to a previous Excel VBA Active X ListBox question resolved by EE expert dlmille.

I have a 'Menu' worksheet which, when activated, adds items to a ListBox. It works fine when I click on the 'Menu' worksheet tab from another sheet, but when I first open the workbook and it defaults to the 'Menu' worksheet the ListBox is empty. I have to move to another sheet, and then back to the 'Menu' sheet to add the list items.

Is there any way I can display the list items when the workbook first opens without having to display another sheet and then move back to the 'Menu' sheet ?

Also, can I display the first list item as the default selected item, as all the items are unselected when they are first displayed. I've attached the code to show you the procedure.

Thanks
Toco

Private Sub Worksheet_Activate()
   ListBox1.Clear
   For Row = 2 to 20
      ListBox1.AddItem Sheets("Tables").Cells(Row,10).Value
   Next Row
End Sub

Open in new window

rspahitz

I think you want to use the Open method of the workbook, found in ThisWorkbook module:

Private Sub Workbook_Open()

End Sub

And you may need to reference the sheet also:

Sheets("{ListSheet}").ListBox1.AddItem Sheets("Tables").Cells(Row,10).Value

where {ListSheet} is the name of the sheet where the listbox is located.

rspahitz

>can I display the first list item as the default selected item

Listbox1.Listindex=0

Tocogroup

ASKER

OK.

I copied the code I have in my Private Sub Worksheet_Activate() procedure to your suggested Private Sub Workbook_Open() procedure in ThisWorkbook.

However, when I opened the workbook it gave me a runtime error '424' Object required on the ListBox1.Clear statement in the Sub Workbook_Open() procedure

Is it confused as to which sheet it is opening ?

toco

Norie

There's a few ways you could approach it.

One would be with something like this:

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Application.Goto Worksheets("Tables").Range("A1")
    Application.Goto Worksheets("Menu").Range("A1")
    Application.ScreenUpdating = True
End Sub

Open in new window

Another would be to populate the listbox as soon as the workbook is open.

That brings up a question though, when do you actually want it populated?

Do you really want to populate it each time the menu sheet is activated?

Tocogroup

ASKER

That's a really good question. I want the listbox to be populated when the workbook is first opened because the list won't change each time the menu sheet is activated, thereafter.

Does that make it easier ?

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tocogroup

ASKER

Excellent ! I can now dispense with the sheet activate procedure and just use your Workbook_Open procedure.
Many thanks for this. I've learned a lot.
Toco