Populating Listbox on workbook open

[Spoony]

I'm trying to populate a listbox that I have put on my sheet.

Using the Workbook_Open:

with ListBox1
.additem "AAA"
.additem "BBB"
end with

however, it gives me a Run-time error 91

"Object variable or with block variable not set"

Can anyone please give me some clues as to what I need to do.

Many thanks

Steve

 

[VoG]

Try like this

With ActiveSheet.ListBoxes("List Box 1")
    .AddItem "AAA"
    .AddItem "BBB"
End With

 

[Spoony]

Thanks Peter

It didn't work, I even took out the spacing in the name to see if it would make a difference


Private Sub Workbook_Open()

With ActiveSheet.ListBoxes("ListBox1")
.Clear
.AddItem "Horizontal"
.AddItem "Manufacturing"
.AddItem "AEC"
.AddItem "Retail"
End With

End Sub

 

[VoG]

Where is this list box from? Is it a Forms list box or a Controls (ActiveX) list box?

 

[Spoony]

activeX

 

[VoG]

In that case

With ActiveSheet.OLEObjects("ListBox1").Object
    .AddItem "AAA"
    .AddItem "BBB"
End With

 

[Spoony]

Brilliant, thank you very much

 

[billritz]

Just a note: I tried adding some dynamically to an existing listbox, but the .additem doesn't work if you have ListFillRange set - you get a permissions error.

BTW, does anybody know how to initialize this to no selection in workbook to force the user to make one? Is that possible in a ole listbox?