compariniaa
07-14-2006, 07:53 AM
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
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