Offset the result
 

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

Jock

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

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

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

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

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

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