Solved: UserForm to retrieve sheet names from another workbook

[compariniaa]

I have a userform that I'm trying to get to compile a list of sheet names in another workbook, but I keep getting "error 9" (subscript out of range). what am I doing wrong?

Private Sub UserForm_Initialize()
Dim s As Long
Dim FCST_Tool As Workbook
Dim Upsides As Workbook

Set FCST_Tool = Workbooks(Sheets("Sheet1").Range("A1").Value)
Set Upsides = Workbooks(Sheets("Sheet1").Range("A2").Value)

For s = 1 To Upsides.Worksheets.Count
With ListBox1
.AddItem Worksheets(s).Name
End With
Next s

ListBox1.ListIndex = 0
End Sub the code that calls this userform first records the name of the activeworkbook in Sheet1, cell A1 (because I have yet to learn how to declare/create constants for a project)
the code then opens another workbook (designated by the user) and records the name of that workbook in Sheet2, cell A2. then it selects the original workbook (so the userform can define the workbooks)
finally, it calls the userform

[OBP]

Andre, I am not sure why you get the error, I think the name of the second workbook may be causing the error. If you activate the second workbook then your code will work with this minor modification

Workbooks("Book2").Activate
For s = 1 To Application.Worksheets.Count
MsgBox Worksheets(s).Name ' set to an array here
Next s
I have just tested it and it worked fine, if you set the names against a variable array then re-activate the first sheet you can transfer the variable array values to your list box.

You could try using the message box method to see what your code returns with something like this
MsgBox s & " = " & Worksheets(s).Name

[OBP]

Andre, I have just checked the syntax and what is wrong with your statement is that you need to prefix the workbook name with workbooks like so
For s = 1 To Workbooks("Book2").Worksheets.Count
MsgBox Worksheets(s).Name
Next s

Which means you do not have to activate the workbook first etc.

[compariniaa]

I got it cause you were on the right track tony, thanks for pointing me in the right direction. but i think you may have been mistaken because i had defined "upsides" (the other workbook) as a workbook, so i wouldn't need to add the "workbooks("workbook")"part.

but what i was missing was saying which workbook the "worksheets(s).name" part referred to. it works now, thanks for your help tony

[mdmackillop]

Can you post your final code for others?