Move user to new worksheet with cell click

[Guest]

When a user clicks on cell A1 in Sheet1, they are moved to Sheet2, cell A1.
What would the code be?

Thanks, Phil

 

[Tom Ogilvy]

Assuming A1 is not already the activeCell on Sheet1

right click on the the tab for sheet1 and paste in the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then
Worksheets("Sheet2").Activate
Worksheets("Sheet2").Range("A1").Select
End If
End Sub

 

[Don Guillett]

right click sheet tab>view code>copy/paste this>SAVE
You might want to restrict to a certain area of the worksheet such as this
as 1st line.
if target.row<3 or target.column<>1 then exit sub

to automatically goto the same cell on sheet 2
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Goto Sheets("sheet2").Range(Target.Address)
End Sub

to goto cell a1 on sheet 2 if cell a1 on sheet 1 clicked.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then _
Application.Goto Sheets("sheet2").Range("a1")
End Sub

 

[Guest]

Thanks, Tom, it works fine. Could we take this a step further?

User clicks cell

 

[Guest]

Tom,

I stumbled on the "post" button before I was finished. My next step:

User Click cell moved to worksheet

A1 Sheet2
A2 Sheet3
A3 Sheet4
and so on....

Thanks, Phil

 

[Tom Ogilvy]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Worksheets("Sheet2").Activate
Worksheets("Sheet2").Range("A1").Select

End Sub

 

[Tom Ogilvy]

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

 

[Guest]

Tom, I'm running you around in circles, and I apolojize. What I need:

User clicks Moves to worksheet
B11 Letter
B12 Consent
B14 Statement
etc. Cell addresses and worksheet names are all different.
Sorry for the confusion - I thought this would be more straignt forward.

Thanks, Phil

 

[Don Guillett]

From VBA HELP

Using Select Case Statements
Use the Select Case statement as an alternative to using ElseIf in
If...Then...Else statements when comparing one expression to several
different values. While If...Then...Else statements can evaluate a different
expression for each ElseIf statement, the Select Case statement evaluates an
expression only once, at the top of the control structure.

In the following example, the Select Case statement evaluates the
performance argument that is passed to the procedure. Note that each Case
statement can contain more than one value, a range of values, or a
combination of values and comparison operators. The optional Case Else
statement runs if the Select Case statement doesn't match a value in any of
the Case statements.

Function Bonus(performance, salary)
Select Case performance
Case 1
Bonus = salary * 0.1
Case 2, 3
Bonus = salary * 0.09
Case 4 To 6
Bonus = salary * 0.07
Case Is > 8
Bonus = 100
Case Else
Bonus = 0
End Select
End Function

 

[Guest]

Don,

Thanks for your reply, appreciate it. Not being a programmer, could you
provide the "Case" code? There will be about 40 different-named worksheets
involved and I think I can modify the code to make it work for all cases.
Again, I appreciate your and Tom's help.

Phil

 

[Don Guillett]

The best way to learn is by doing. Try the example and play with it.

 

[Tom Ogilvy]

If you still want the action on the click in column A and the sheet names
are in the adjacent cell in column B then you can do

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