Disposable/Dynamic Listbox

[HWL]

I'm trying to have a macro that creates a list box of the names of the open Excel files so the user can select, which will continue my code on the file selected.

I thought something like this would work but it isn't:

Dim wbk As Workbook
For Each wbk In Workbooks
'loop through the Open workbooks
If wbk.Name <> ThisWorkbook.Name Then
'exclude this workbook from the Loop
else
ListBox1.additem wbk.name
next wbk
Endif
​

This fails as saying the Next needs a For even though there is a FOR.

 

[Norie]

The End If and Next are the wrong way round.

 

[HWL]

The End If and Next are the wrong way round.

duh, that was dummy mistake, but it still doesn't recognize the ListBox1

 

[Norie]

Where is the listbox and where is the code?

 

[HWL]

Where is the listbox and where is the code?

Well, I've tried both ways. I created a userform, added a list box to it then ran the code. But ideally I'd like it to create the listbox when the code is ran and delete it when done.

The error I get is:
Run Error '424' - Object Required

I'd assume this means that the ListBox1 doesn't exist therefore the "additem" code fails.

 

[Norie]

So where is the code located?

PS Why do you want to delete the listbox? You could just hide it, or if it's on a userform close the userform.