Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a Cell Ref When Clicking in another Cell
I'd like to be able to change a cell reference in one cell based upon which
cell I've currently clicked in. Example. My formulas are all entered in Sheet1. If I click in cell B1 on Sheet1, the value in cell A1 on Sheet1refers to Sheet2!A1. If instead, I click in cell C1 on Sheet1, the value in A1 on Sheet1 will refer to Sheet3!A1 and so on. I'm only somewhat fluent in VBA. Any help would be appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a Cell Ref When Clicking in another Cell
In the Microsoft Excel Objects folder in your VBA Project, click on
Sheet1. Using your example below, add this into Sheet1's module. Private Sub Worksheet_SelectionChange(ByVal Target As Range) if Target.Address = "$B$1" Then Cells(1,1).Formula = "=Sheet2!A1" ElseIf Target.Address = "$C$1" Then Cells(1,1).Formula = "=Sheet3!A1" End If End Sub Simply extend this same pattern for however many different scenarios you have. HTH -Jeff- Chad Borman wrote: I'd like to be able to change a cell reference in one cell based upon which cell I've currently clicked in. Example. My formulas are all entered in Sheet1. If I click in cell B1 on Sheet1, the value in cell A1 on Sheet1refers to Sheet2!A1. If instead, I click in cell C1 on Sheet1, the value in A1 on Sheet1 will refer to Sheet3!A1 and so on. I'm only somewhat fluent in VBA. Any help would be appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a Cell Ref When Clicking in another Cell
Thanks for your help, JW. It worked (with one minor change - I had to
enclose the sheet names between single quotes). Private Sub Worksheet_SelectionChange(ByVal Target As Range) if Target.Address = "$B$1" Then Cells(1,1).Formula = "='Sheet2'!A1" ElseIf Target.Address = "$C$1" Then Cells(1,1).Formula = "='Sheet3'!A1" End If End Sub "JW" wrote: In the Microsoft Excel Objects folder in your VBA Project, click on Sheet1. Using your example below, add this into Sheet1's module. Private Sub Worksheet_SelectionChange(ByVal Target As Range) if Target.Address = "$B$1" Then Cells(1,1).Formula = "=Sheet2!A1" ElseIf Target.Address = "$C$1" Then Cells(1,1).Formula = "=Sheet3!A1" End If End Sub Simply extend this same pattern for however many different scenarios you have. HTH -Jeff- Chad Borman wrote: I'd like to be able to change a cell reference in one cell based upon which cell I've currently clicked in. Example. My formulas are all entered in Sheet1. If I click in cell B1 on Sheet1, the value in cell A1 on Sheet1refers to Sheet2!A1. If instead, I click in cell C1 on Sheet1, the value in A1 on Sheet1 will refer to Sheet3!A1 and so on. I'm only somewhat fluent in VBA. Any help would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add or Delete borders of a cell by clicking on that cell | Excel Discussion (Misc queries) | |||
How do I edit a cell in Excel without clicking on the cell first? | Excel Worksheet Functions | |||
how to change cell colour by simply clicking on it | Excel Discussion (Misc queries) | |||
Can I toggle the value of a cell by clicking directly on the cell? | Excel Discussion (Misc queries) | |||
Clicking Cell Link Changes Cell on Another Sheet | Excel Discussion (Misc queries) |