Identifying which workbook a worksheet is in

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using Excel 97,

I am populating a list in a userform with

' This sets up the array for the workbooks
'*******************************************
arSubWorkBooks = Array("Time Off - (0000-0999)", "Time Off -
(1000-1999)", _
"Time Off - (2000-2999)", "Time Off - (3000-3999)", "Time Off -
(4000-4999)", _
"Time Off - (5000-5999)", "Time Off - (6000-6999)")

' This imports the data from each Sub WorkBook
'*******************************************
For i = 0 To UBound(arSubWorkBooks)

' search for all sheets and get values
Workbooks(arSubWorkBooks(i)).Activate

For Each PAGE In ActiveWorkbook.Worksheets

If PAGE.Visible = xlVeryHidden Then

frmSelect.MyList.AddItem (PAGE.Name)

End If

Next PAGE

Next i

frmSelect.Show

The code in the userform is:


Private Sub MyList_Click()

***I need some here to identify which workbook to activate ****

if ActiveWorkbook.Sheets(MyList.Value).Visible = xlVeryHidden Then
ActiveWorkbook.Sheets(MyList.Value).Visible = True

ActiveWorkbook.Sheets(MyList.Value).Select

End If

Range("A1").Select


Many thanks
 
Mark

I think you'll need to store that information unless every worksheet is
unique. To store, make sure you listbox has 2 columns. You can make the
width of the second column zero so that it's not seen. Then adjust your
code similar to this:
For Each PAGE In ActiveWorkbook.Worksheets

If PAGE.Visible = xlVeryHidden Then
With frmSelect.MyList
.AddItem (PAGE.Name)
Click to expand...
.List(.ListCount-1,1) = PAGE.Parent.Name
End With
End If

Next PAGE
[snip]


Private Sub MyList_Click()

***I need some here to identify which workbook to activate ****
Click to expand...

Dim wb as Workbook

Set wb = Workbooks(MyList.List(MyList.ListIndex,1))

'Now change ActiveWorkbook to wb
if ActiveWorkbook.Sheets(MyList.Value).Visible = xlVeryHidden Then
ActiveWorkbook.Sheets(MyList.Value).Visible = True

ActiveWorkbook.Sheets(MyList.Value).Select

End If
Click to expand...

If you want to get rid of that array of workbook names, you could

Dim i as Long

For i = 0 to 6999 Step 1000
For Each PAGE in Workbooks("Time Off - (" & Format(i,"0000") & "-" &
Format(i+999,"0000") & ")").Worksheets
 
Back
Top