Avatar of Tocogroup
Tocogroup
Flag for United Kingdom of Great Britain and Northern Ireland 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

Microsoft Excel

Avatar of undefined
Last Comment
Tocogroup

8/22/2022 - Mon
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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.