Add Items to listbox

[MelG]

The code below is supposed to take the filenames from all open excel files and add them to the listbox. I keep getting the error "Object doesnt support this property or method". I can confirm List Box 2 definity exists within Sheet 2 of the workbook. Can anyone help?

Sub MyFileNameArray()
    Dim wb As Workbook
    Dim wbs As Workbooks
    Set wbs = Application.Workbooks
    For Each wb In wbs
        Worksheets("Sheet2").ListBox2.AddItem wb.Name
    Next wb
End Sub

 

[MelG]

Not sure why my post has bunched up all the code. 2nd attempt to post the code in legible format.Sub MyFileNameArray() Dim wb As Workbook Dim wbs As WorkbooksSet wbs = Application.Workbooks For Each wb In wbs Worksheets("Sheet2").ListBox2.AddItem wb.Name Next wb End Sub

 

[MickG]

['code]
Post you code (Here), between square brackets as show
[/code]

FYI:- Your code worked for me !!

 

[Zack Barresse]

Mel, I edited your post. Please surround your code with the CODE tags. When posting (not using Quick Reply) look for the pound sign (#) with your code selected.

 

[MelG]

Thanks guys. I tried replicating in a new workbook and still no luck. Mick G, can you confirm the method used to create you listbox? Just trying to figure out why it worked for you. I created the listbox using the Forms toolbar in excel.

 

[MickG]

That could be it, you need "ActiveX" !!!
If you use "Forms" you need something Like this:-

Sub MyFileNameArray()
Dim wb As Workbook
Dim wbs As Workbooks
Set wbs = Application.Workbooks
For Each wb In wbs
    Worksheets("Sheet2").Shapes("List Box 1").ControlFormat.AddItem wb.Name
Next wb
End Sub