| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset the result
If cell L5 has focus, can call L1 display the contents of L4 + 50?
-- Traa Dy Liooar Jock |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Offset the result
Hi,
With L5 as the active cell try this Range("L1").Value = ActiveCell.Offset(, -1) + 50 Mike "Jock" wrote: If cell L5 has focus, can call L1 display the contents of L4 + 50? -- Traa Dy Liooar Jock |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Offset the result
OOPS,
that should have been Range("L1").Value = ActiveCell.Offset(-1) + 50 Mike "Mike H" wrote: Hi, With L5 as the active cell try this Range("L1").Value = ActiveCell.Offset(, -1) + 50 Mike "Jock" wrote: If cell L5 has focus, can call L1 display the contents of L4 + 50? -- Traa Dy Liooar Jock |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Offset the result
Hi Mike,
I have placed your code within a Private Sub DoubleClick (see below). This works until the user double cluicks a cell with no data in it when the code stops working until the spreadsheet is closed down and opened again. Can something be put in to ignore double clicks in empty cells? And is there such a thing as a "single click" alternative? Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Application.EnableEvents = False If Not Intersect(Target, Me.Range("k6:k30")) Is Nothing Then With Target If .Value < "" Then Range("L2").Value = ActiveCell.Offset(0, -2) + 50 Application.EnableEvents = True End If End With End If End Sub Thanks. -- Traa Dy Liooar Jock "Mike H" wrote: OOPS, that should have been Range("L1").Value = ActiveCell.Offset(-1) + 50 Mike "Mike H" wrote: Hi, With L5 as the active cell try this Range("L1").Value = ActiveCell.Offset(, -1) + 50 Mike "Jock" wrote: If cell L5 has focus, can call L1 display the contents of L4 + 50? -- Traa Dy Liooar Jock |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Offset the result
Hi,
You were disabling events at the wrong time Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Not Intersect(Target, Me.Range("k6:k30")) Is Nothing Then With Target If .Value < "" Then Application.EnableEvents = False Range("L2").Value = ActiveCell.Offset(0, -2) + 50 Application.EnableEvents = True End If End With End If End Sub Mike "Jock" wrote: Hi Mike, I have placed your code within a Private Sub DoubleClick (see below). This works until the user double cluicks a cell with no data in it when the code stops working until the spreadsheet is closed down and opened again. Can something be put in to ignore double clicks in empty cells? And is there such a thing as a "single click" alternative? Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Application.EnableEvents = False If Not Intersect(Target, Me.Range("k6:k30")) Is Nothing Then With Target If .Value < "" Then Range("L2").Value = ActiveCell.Offset(0, -2) + 50 Application.EnableEvents = True End If End With End If End Sub Thanks. -- Traa Dy Liooar Jock "Mike H" wrote: OOPS, that should have been Range("L1").Value = ActiveCell.Offset(-1) + 50 Mike "Mike H" wrote: Hi, With L5 as the active cell try this Range("L1").Value = ActiveCell.Offset(, -1) + 50 Mike "Jock" wrote: If cell L5 has focus, can call L1 display the contents of L4 + 50? -- Traa Dy Liooar Jock |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Offset the result
Thats better!
Quick question, could the GotFocus event be used in this scenario rather than the BeforeDoubleClick? Users will moan..... -- Traa Dy Liooar Jock "Mike H" wrote: Hi, You were disabling events at the wrong time Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Not Intersect(Target, Me.Range("k6:k30")) Is Nothing Then With Target If .Value < "" Then Application.EnableEvents = False Range("L2").Value = ActiveCell.Offset(0, -2) + 50 Application.EnableEvents = True End If End With End If End Sub Mike "Jock" wrote: Hi Mike, I have placed your code within a Private Sub DoubleClick (see below). This works until the user double cluicks a cell with no data in it when the code stops working until the spreadsheet is closed down and opened again. Can something be put in to ignore double clicks in empty cells? And is there such a thing as a "single click" alternative? Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Application.EnableEvents = False If Not Intersect(Target, Me.Range("k6:k30")) Is Nothing Then With Target If .Value < "" Then Range("L2").Value = ActiveCell.Offset(0, -2) + 50 Application.EnableEvents = True End If End With End If End Sub Thanks. -- Traa Dy Liooar Jock "Mike H" wrote: OOPS, that should have been Range("L1").Value = ActiveCell.Offset(-1) + 50 Mike "Mike H" wrote: Hi, With L5 as the active cell try this Range("L1").Value = ActiveCell.Offset(, -1) + 50 Mike "Jock" wrote: If cell L5 has focus, can call L1 display the contents of L4 + 50? -- Traa Dy Liooar Jock |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Offset the result
Hi,
Thers is no getfocus event for a cell only selection_change. Mike "Jock" wrote: Thats better! Quick question, could the GotFocus event be used in this scenario rather than the BeforeDoubleClick? Users will moan..... -- Traa Dy Liooar Jock "Mike H" wrote: Hi, You were disabling events at the wrong time Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Not Intersect(Target, Me.Range("k6:k30")) Is Nothing Then With Target If .Value < "" Then Application.EnableEvents = False Range("L2").Value = ActiveCell.Offset(0, -2) + 50 Application.EnableEvents = True End If End With End If End Sub Mike "Jock" wrote: Hi Mike, I have placed your code within a Private Sub DoubleClick (see below). This works until the user double cluicks a cell with no data in it when the code stops working until the spreadsheet is closed down and opened again. Can something be put in to ignore double clicks in empty cells? And is there such a thing as a "single click" alternative? Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Application.EnableEvents = False If Not Intersect(Target, Me.Range("k6:k30")) Is Nothing Then With Target If .Value < "" Then Range("L2").Value = ActiveCell.Offset(0, -2) + 50 Application.EnableEvents = True End If End With End If End Sub Thanks. -- Traa Dy Liooar Jock "Mike H" wrote: OOPS, that should have been Range("L1").Value = ActiveCell.Offset(-1) + 50 Mike "Mike H" wrote: Hi, With L5 as the active cell try this Range("L1").Value = ActiveCell.Offset(, -1) + 50 Mike "Jock" wrote: If cell L5 has focus, can call L1 display the contents of L4 + 50? -- Traa Dy Liooar Jock |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Offset/Match when there is more than 1 result | Excel Worksheet Functions | |||
| Offset a formula result | Excel Worksheet Functions | |||
| large function result as reference for offset function | Excel Discussion (Misc queries) | |||
| Array Formula - Offset Result Problem | Excel Programming | |||
| Select row (with offset) from VB formula result | Excel Programming | |||
Linear Mode
