Worsheet macro

[qleader]

I have a spreadsheet with 100's of worksheets in it. They are listed by numerical order. I need to write a macro routine that has a user form that can list the worksheets and then allow the user to enter the worksheet number and go to that worksheet so that I can manually edit it.


Thanks,
David

 

[dsi]

There is actually a built in tool that lists all of the sheets. In the lower left corner of the application, there are arrows for traversing the worksheet tabs. If you right-click any of the arrows, a list of worksheets is displayed. It only displays 15 sheet names and an option for More Worksheets. If you select the More Worksheets option, you will be presented with a complete list of sheets.

 

[LSTAN]

hi qleader

Here is how, assume the userform has a combobox;

Private Sub UserForm_Initialize()

Dim sheetNames() As String, sheetCount As Integer

' Get the number of sheets
sheetCount = ActiveWorkbook.Sheets.Count

' Redimension the array
ReDim sheetNames(1 To sheetCount)

' Fill combo-box with sheetnames
For i = 1 To sheetCount
sheetNames(i) = ActiveWorkbook.Sheets(i).Name
Me.ComboBox1.AddItem sheetNames(i)
Next i

End Sub

To go to the selected sheet, use the Change event of the Combobox.

Private Sub ComboBox1_Change()

Dim selSheet As String

selSheet = Me.ComboBox1.Text

Unload Me 'unload the userform

Sheets(selSheet).Select

End Sub


rgrds
LSTAN