listbox items selection

[PANKAJUTEKAR]

Dear All,

i have taken listbox and there are two items in it.
mot
sfu

the folloowing is the code.

Private Sub Workbook_Open()
'ListBox1.Clear
ListBox1.AddItem "Motor"
ListBox1.AddItem "SFU"

The problem is-
Error 424 i got - object required.

i try some r&d, i think my excel sheet4 not able recognize this listbox1.

Can anyone give me the idea how to solve it?

 

[VoG]

Maybe like this if the listbox is on a sheet

Private Sub Workbook_Open()
'ListBox1.Clear
Sheets("Sheet1").ListBoxes("List Box 1").AddItem "Motor"
Sheets("Sheet1").ListBoxes("List Box 1").AddItem "SFU"
End Sub

 

[Tom Urtis]

Assuming this a listbox from the activex control toolbox which is embedded onto your worksheet, the "object required" is your worksheet's name or codename.

This works, using your Sheet4 as a reference.

Private Sub Workbook_Open()
With Worksheets("Sheet4").ListBox1
.Clear
.AddItem "Motor"
.AddItem "SFU"
End With
End Sub

 

[PANKAJUTEKAR]

Dear Sir,

I got the following error.

Runtime error - 9
subscript out of range

 

[PANKAJUTEKAR]

Its working Sir.
Nice!!
Thank You Experts.
You all genius.