Navigating among worksheets

[Guest]

I need macro code to do the following: User clicks on a cell in a master
worksheet and is taken to cell A1 in another worksheet. For example:

Click
Cell Worksheet
----- -------------
B14 Atlanta
B15 Philadelphia
B16 Jacksonville
Etc.

There are thirty different click cells on the master worksheet and thirty
different target worksheets.

Thanks, Phil

 

[Guest]

Create hyperlinks in the 'click Cell's.
INSERT / HYPERLINK
Select 'Place in this document' in the box on the left
Select the sheet you want the hyperlink to point to.
Done

HTH,
Gary Brown

 

[Tom Ogilvy]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim sh as Worksheet
if Target.count > 1 then exit sub
If Target.Column = 2 Then
If Target.Row >=14 and Target.Row <= 43 Then
on Error Resume next
set sh = Worksheets(Target.offset(0,1).Value)
On error goto 0
if not sh is nothing then
sh.Activate
sh.Range("A1").Select
End if
End If
End If
End Sub

 

[Guest]

Tom, Thanks for your reply. For some reason, the macro doesn’t work – no
error though. I have requested this macro in the early stages of development
of this project, anticipating the need to quickly navigate the user through
all the worksheets. Two things come to mind:
1.) Not all of the target worksheets have been added yet. Additionally, I
may have to add or delete worksheets (cities) as things progress, which would
have their corresponding click cell.
2.) The click cells on the master worksheet (named “Actionsâ€) are not
necessarily consecutive, as in the example below.

Click Target
Cell Worksheet
------ ------------
B11 Atlanta
B12 Philadelphia
B14 Jacksonville
B16 Miami
B17 Montgomery
B18 Columbus
B23 Indianapolis

Should the code be looking for the title of the worksheet and then acting on
it?

 

[Lonnie M.]

Hi, if the user double clicks in the cell you could use something like
this to take them to that sheet (assuming that the sheets are named
after the cities; place this in the sheet module):
Public Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range,
Cancel As Boolean)
Dim C$
Cancel = True
C = Target.Address(False, False)
If C = "B11" Then
Worksheets("Atlanta").Select
ElseIf C = "B12" Then
Worksheets("Philadelphia").Select
ElseIf C = "B14" Then
Worksheets("Jacksonville").Select
ElseIf C = "B16" Then
Worksheets("Miami").Select
ElseIf C = "B17" Then
Worksheets("Montgomery").Select
ElseIf C = "B18" Then
Worksheets("Columbus").Select
ElseIf C = "B23" Then
Worksheets("Indianapolis").Select
End If
End Sub


HTH--Lonnie M.

 

[Guest]

Thanks, Lonnie - it works.

 

[Tom Ogilvy]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim sh as Worksheet
if Target.count > 1 then exit sub
If Target.Column = 1 Then
on Error Resume next
set sh = Worksheets(Target.offset(0,1).Value)
On error goto 0
if not sh is nothing then
sh.Activate
sh.Range("A1").Select
End if
End If
End Sub

Misunderstood your post.

It worked a couple of days ago when I responded to your post then and I have
taken out my modifications, so it works now as well.

 

[Tom Ogilvy]

One more try. Your original post (not today) was clicking in column A and
going to the worksheet in column B. So my macro reflected that. i have now
modified it for clicking on the name of the sheet itself with the assumption
the cell with the name is in column B.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim sh As Worksheet
If Target.Count > 1 Then Exit Sub
If Target.Column = 2 Then
On Error Resume Next
Set sh = Worksheets(Target.Value)
On Error GoTo 0
If Not sh Is Nothing Then
sh.Activate
sh.Range("A1").Select
End If
End If
End Sub

If this doesn't work then I guess I don't understand what you want.

 

[Guest]

Tom, This works! When the user clicks a cell, the macro works to find a
worksheet with the same name as the cell clicked on...right? The restriction
in this macro is that the "click" cell must be in the second column...true?
Thanks for taking the time with this.
Phil

 

[Tom Ogilvy]

the click must be in column B. That was my understanding of the revised
requirement. If the requirement is different, then state what the
requirement is.

When the user clicks a cell, the macro works to find a
worksheet with the same name as the cell clicked on...right?

Answer: Yes

The restriction
in this macro is that the "click" cell must be in the second

column...true?

Answer: True

 

[Guest]

Tom, Thanks for the insight. This is working exactly as I need. Phil