Move user to new worksheet with cell click
 

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

Thanks, Phil

Move user to new worksheet with cell click
 

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

--
Tom Ogilvy


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

Thanks, Phil

Move user to new worksheet with cell click
 

right click sheet tabview codecopy/paste thisSAVE
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
--
Don Guillett
SalesAid Software

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

Thanks, Phil

Move user to new worksheet with cell click
 

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

User clicks cell



"Tom Ogilvy" wrote:

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

--
Tom Ogilvy


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

Thanks, Phil

Move user to new worksheet with cell click
 

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" wrote:

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

--
Tom Ogilvy


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

Thanks, Phil

Move user to new worksheet with cell click
 

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

End Sub


--
Regards,
Tom Ogilvy


"Phil Hageman" wrote in message
...
Thanks, Tom, it works fine. Could we take this a step further?

User clicks cell



"Tom Ogilvy" wrote:

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

--
Tom Ogilvy


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

Thanks, Phil

Move user to new worksheet with cell click
 

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

--
Regards,
Tom Ogilvy


"Phil Hageman" wrote in message
...
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" wrote:

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

--
Tom Ogilvy


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

Thanks, Phil

Move user to new worksheet with cell click
 

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


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Phil Hageman" wrote in message
...
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" wrote:

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

--
Tom Ogilvy


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

Thanks, Phil

Move user to new worksheet with cell click
 

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

--
Don Guillett
SalesAid Software

"Phil Hageman" wrote in message
...
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


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Phil Hageman" wrote in message
...
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" wrote:

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

--
Tom Ogilvy


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

Thanks, Phil

Move user to new worksheet with cell click
 

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" wrote:

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

--
Don Guillett
SalesAid Software

"Phil Hageman" wrote in message
...
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


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Phil Hageman" wrote in message
...
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" wrote:

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

--
Tom Ogilvy


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

Thanks, Phil

Move user to new worksheet with cell click
 

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

--
Don Guillett
SalesAid Software

"Phil Hageman" wrote in message
...
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" wrote:

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

--
Don Guillett
SalesAid Software

"Phil Hageman" wrote in message
...
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


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Phil Hageman" wrote in
message
...
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" wrote:

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

--
Tom Ogilvy


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

Thanks, Phil

Move user to new worksheet with cell click
 

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

--
Regards,
Tom Ogilvy


"Phil Hageman" wrote in message
...
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


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Phil Hageman" wrote in message
...
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" wrote:

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

--
Tom Ogilvy


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

Thanks, Phil