Offset the result

[Jock]

If cell L5 has focus, can call L1 display the contents of L4 + 50?

 

[Mike H]

Hi,

With L5 as the active cell try this

Range("L1").Value = ActiveCell.Offset(, -1) + 50

Mike

 

[Mike H]

OOPS,

that should have been

Range("L1").Value = ActiveCell.Offset(-1) + 50

Mike

 

[Jock]

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.

 

[Mike H]

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]

Thats better!
Quick question, could the GotFocus event be used in this scenario rather
than the
BeforeDoubleClick? Users will moan.....

 

[Mike H]

Hi,

Thers is no getfocus event for a cell only selection_change.

Mike