Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset to New Sheet
In "Sheet1", any cell may be selected (except in Columns A-E).
I need code that will will select the cell in "Sheet2", in the same Row as in "Sheet1', but offset 4 columns to the left. Thus if Sheet1.F5 is selected, I need code that will select the cell Sheet2.B5. Thanks for any help |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset to New Sheet
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const WS_RANGE As String = "F:IV" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Worksheets("Sheet2").Activate ActiveSheet.Range(.Address).Offset(0, -4).Select End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Kanga 85" wrote in message ... In "Sheet1", any cell may be selected (except in Columns A-E). I need code that will will select the cell in "Sheet2", in the same Row as in "Sheet1', but offset 4 columns to the left. Thus if Sheet1.F5 is selected, I need code that will select the cell Sheet2.B5. Thanks for any help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset to New Sheet
Thanks Bob,
I was not explict enuf. The bit of code I need is to be inserted into a Macro I'm now using which, when called, does various things in Sheet1. I now want to do an additional job in Sheet2 as part of the same Macro and I just need to know how to select the appropriate cell. Thanks. "Bob Phillips" wrote: Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const WS_RANGE As String = "F:IV" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Worksheets("Sheet2").Activate ActiveSheet.Range(.Address).Offset(0, -4).Select End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Kanga 85" wrote in message ... In "Sheet1", any cell may be selected (except in Columns A-E). I need code that will will select the cell in "Sheet2", in the same Row as in "Sheet1', but offset 4 columns to the left. Thus if Sheet1.F5 is selected, I need code that will select the cell Sheet2.B5. Thanks for any help |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset to New Sheet
If activeCell.Column = 5 then
application.Goto Worksheets("Sheet2").Range(ActiveCell.Address).Off set(0,-4) End if -- Regards, Tom Ogilvy "Kanga 85" wrote in message ... In "Sheet1", any cell may be selected (except in Columns A-E). I need code that will will select the cell in "Sheet2", in the same Row as in "Sheet1', but offset 4 columns to the left. Thus if Sheet1.F5 is selected, I need code that will select the cell Sheet2.B5. Thanks for any help |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset to New Sheet
"Tom Ogilvy" wrote: If activeCell.Column = 5 then application.Goto Worksheets("Sheet2").Range(ActiveCell.Address).Off set(0,-4) End if -- Regards, Tom Ogilvy "Kanga 85" wrote in message ... In "Sheet1", any cell may be selected (except in Columns A-E). I need code that will will select the cell in "Sheet2", in the same Row as in "Sheet1', but offset 4 columns to the left. Thus if Sheet1.F5 is selected, I need code that will select the cell Sheet2.B5. Thanks for any help |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset to New Sheet
Thanks Tom
"Tom Ogilvy" wrote: If activeCell.Column = 5 then application.Goto Worksheets("Sheet2").Range(ActiveCell.Address).Off set(0,-4) End if -- Regards, Tom Ogilvy "Kanga 85" wrote in message ... In "Sheet1", any cell may be selected (except in Columns A-E). I need code that will will select the cell in "Sheet2", in the same Row as in "Sheet1', but offset 4 columns to the left. Thus if Sheet1.F5 is selected, I need code that will select the cell Sheet2.B5. Thanks for any help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula to look up ref in one sheet and offset in another | Excel Worksheet Functions | |||
Copy link to offset sheet | Excel Worksheet Functions | |||
Offset in another sheet | Excel Discussion (Misc queries) | |||
Using offset more than once on the same sheet | Excel Worksheet Functions | |||
Sheet Offset - is this possible? | Excel Programming |