If then go to

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

Guest

Is there a way to input a date or word that would cause the worksheet to open
another worksheet? I am trying to be able to plug in a date and have the
date generate which sheet I should be using. This is for the purpose of
rotating my schedules.
 
First enter this macro in the worksheet event code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1")
If Intersect(Target, r) Is Nothing Then Exit Sub
v = r.Value
For Each ws In Worksheets
If ws.Name = v Then
ws.Activate
Exit Sub
End If
Next
End Sub

The macro looks at your entries in cell A1. If you enter a valid tab name
in A1, you will jump to the corresponding worksheet. If you happen to enter
text that is not a valid tabe name, then no jump is made.


Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm
 
Right click sheet tab>view code>copy/paste this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Application.Goto Sheets(Target.Value).Range("a1")
End Sub

or to compensate for errors
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo nono
If Target.Address <> "$A$1" Then Exit Sub
Application.Goto Sheets(Target.Value).Range("a1")
Exit Sub
nono: MsgBox "Not there"
End Sub
 
Is it possible to adjust this code to provide a drop box of the available
worksheets? Like with a data validation drop box.

Many Thanks

Paul Moles
 
Hi Paul:

You do not have to modify the code. Just add the validation drop-down to
the cell.
 
How do I get the Data Validation List (presuming I need to use "List" in the
"Allow" validation criteria ) to "pick" up the sheet names?

Thanks
Paul
 
Add the worksheet names to a sheet and use that list for your DV list.

Private Sub ListSheets()
'list of sheet names starting at A1
Dim Rng As Range
Dim i As Integer
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "List"
Set Rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
Rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
Next Sheet
Set sheetrng = Range("A1", Cells(Rows.Count, ActiveCell.Column).End(xlUp)
sheetrng.Name = "mydvlist"
End Sub

Ib the DV list source use =mydvlist


Gord Dibben MS Excel MVP
 
Just an add-on here.

If all you're looking for is a method to navigate sheets, don't forget the sheet
navigation arrows at bottom left.

You can right-click on one of those to get a pop-up of 15 sheet names and "more
sheets" to scroll through.


Gord
 
Back
Top