Worksheet, selection macro

[Guest]

I need a macro or such like to aid worksheet selection, I intend on using a
dropdown list box or combo box with 12 or more sheets available on it.I'd
like to be able to selct the worksheet needed from the control and then it
goes to that worksheet.

 

[Otto Moehrbach]

Steve
Excel already has something similar to what you describe. Look at the
bottom of any sheet and you see the sheet tabs. Look to the left of the
left-most sheet tab. You see 4 left/right arrows with which you can scroll
the sheet names. Right click inside that group of arrows. This displays
all the sheet names. Click on one and that sheet will become the active
sheet. If you have more sheets that the list can show, the last item in the
list will be "More sheets". Click on that and you will see all the sheets
names. HTH Otto

 

[Guest]

I knew already about the nav bar at the ottom of the screen. what I am after
is a drop down list menu that will be actually on each and every worksheet
itself,positions next to a logo I have place at the top of the worksheets.
Thank you anyway for that

 

[Otto Moehrbach]

Steve
In some out of the way place on one of the sheets, or create another
sheet (hidden), list all the sheet names. Name that list something like
"ShtNames". Then in the same cell in each sheet, create a Data Validation
cell and choose- List. In source: Type "=ShtNames" without the quotes.
In the sheet module of each of these sheets, copy and paste this macro. I
assumed the Data Validation cell is A1. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Address(0, 0) = "A1" Then
Sheets(Target.Value).Activate
Application.EnableEvents = False
Sheets(Target.Value).Range(Target.Address).ClearContents
Application.EnableEvents = True
End If
End Sub

 

[Guest]

Wicked, thank you very much for your help

Steve
In some out of the way place on one of the sheets, or create another
sheet (hidden), list all the sheet names. Name that list something like
"ShtNames". Then in the same cell in each sheet, create a Data Validation
cell and choose- List. In source: Type "=ShtNames" without the quotes.
In the sheet module of each of these sheets, copy and paste this macro. I
assumed the Data Validation cell is A1. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Address(0, 0) = "A1" Then
Sheets(Target.Value).Activate
Application.EnableEvents = False
Sheets(Target.Value).Range(Target.Address).ClearContents
Application.EnableEvents = True
End If
End Sub

 

[Gord Dibben]

Steve

If you have a lot of sheets you can list them using this macro.

Insert a new sheet then run the macro.

Private Sub ListSheets()
'list of sheet names starting at A1
Dim rng As Range
Dim i As Integer
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
Next Sheet
End Sub

When done, follow Otto's advice.


Gord Dibben MS Excel MVP