Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to increment a cell by 1 on mouse click or move to another cel | Excel Discussion (Misc queries) | |||
move to another cell on click | Excel Discussion (Misc queries) | |||
How can user move in locked worksheet but not copy cell contents? | Excel Worksheet Functions | |||
double click mouse, move to referenced wkbk/cell | Setting up and Configuration of Excel | |||
Finding cell that a user click? | Excel Programming |