Macro to Reference Cell to Select Sheet

  • Thread starter Thread starter Tysone
  • Start date Start date
T

Tysone

Here is what I want to do. I want a macro to select a sheet by it
referencing a cell. I have a drop down in Cell A2 with all the sheet
names in my workbook, and what I want to be able to do is have one
marco that basically can work for all the sheets. I could just make a
macro for each sheet, but its a big macro and I have too many sheets.

Here is a little example of what I mean.

Cell A2 = Table1
The Macro would now read
Sheets("Table1").Select

OR Cell A2 = Sheet1
The Macro would now read
Sheets("Sheet1").Select


Thanks for the help

Tyson
 
Are they dropdowns from the forms toolbar?

If yes, you could assign the same macro to each of the dropdowns.

Option Explicit
Sub testme()

Dim myDD As DropDown

Set myDD = ActiveSheet.DropDowns(Application.Caller)

On Error Resume Next
With myDD
Worksheets(.List(.ListIndex)).Select
End With
If Err.Number <> 0 Then
MsgBox "Sheet not available"
Err.Clear
End If
On Error GoTo 0

End Sub

But an alternative solution may be to have a floating toolbar that does the same
function--but it's not in the worksheet????

If yes, you can see this thread for more info:

http://groups.google.com/[email protected]

You could even use it for more than this one workbook.
 
Back
Top