Trigger on leaving cell (of entire column)
 

The code below highlights the entire row I'm working on. When I leav
field $A$1 it popups a messagebox (just by example). I don't need t
make any changes to $A$1. All I have to do is leave it.

My question is.... what code should be replaced by what code to make i
work for every field in Column B? When the current cell is in column
and I press enter or move the down-cursor (or up button) I wish th
trigger to execute. Basically vertical movement in column B shoul
trigger it. I assume something with the IF condition *If Target.Addres
= "$A$1" Then* has to be changed, but what :) Trial and error so far.


Code
-------------------
Dim rTriggerCell As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Triggers highlight of target row
Dim strRow As String
Cells.FormatConditions.Delete

With Target.EntireRow
strRow = .Address
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=COUNTA(" & strRow & ")0"

.FormatConditions(1).Font.Bold = True
.FormatConditions(1).Interior.ColorIndex = 34
End With

'Triggers an action upon user exiting cell A1
On Error Resume Next
Application.EnableEvents = False
'Entered into Trigger cell
If Target.Address = "$A$1" Then
Set rTriggerCell = Target
Application.EnableEvents = True
On Error GoTo 0
Exit Sub
End If

If Not rTriggerCell Is Nothing Then 'They are leaving A1
MsgBox "You just left cell A1", vbInformation, "OzGrid.com"
Set rTriggerCell = Nothing
End If
Application.EnableEvents = True
On Error GoTo 0
End Su
-------------------

--
Lav
-----------------------------------------------------------------------
Lava's Profile: http://www.excelforum.com/member.php...fo&userid=2779
View this thread: http://www.excelforum.com/showthread.php?threadid=47334

Trigger on leaving cell (of entire column)
 

Target.Address gives the address of the cell you have just selected/entered,
not the cell you just left.

In any event, for the same functionality you could use

If Target.column = 2 then

--
Regards,
Tom Ogilvy

"Lava" wrote in message
...

The code below highlights the entire row I'm working on. When I leave
field $A$1 it popups a messagebox (just by example). I don't need to
make any changes to $A$1. All I have to do is leave it.

My question is.... what code should be replaced by what code to make it
work for every field in Column B? When the current cell is in column B
and I press enter or move the down-cursor (or up button) I wish the
trigger to execute. Basically vertical movement in column B should
trigger it. I assume something with the IF condition *If Target.Address
= "$A$1" Then* has to be changed, but what :) Trial and error so far.


Code:
--------------------
Dim rTriggerCell As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Triggers highlight of target row
Dim strRow As String
Cells.FormatConditions.Delete

With Target.EntireRow
strRow = .Address
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=COUNTA(" & strRow & ")0"

.FormatConditions(1).Font.Bold = True
.FormatConditions(1).Interior.ColorIndex = 34
End With

'Triggers an action upon user exiting cell A1
On Error Resume Next
Application.EnableEvents = False
'Entered into Trigger cell
If Target.Address = "$A$1" Then
Set rTriggerCell = Target
Application.EnableEvents = True
On Error GoTo 0
Exit Sub
End If

If Not rTriggerCell Is Nothing Then 'They are leaving A1
MsgBox "You just left cell A1", vbInformation, "OzGrid.com"
Set rTriggerCell = Nothing
End If
Application.EnableEvents = True
On Error GoTo 0
End Sub
--------------------


--
Lava
------------------------------------------------------------------------
Lava's Profile:
http://www.excelforum.com/member.php...o&userid=27793
View this thread: http://www.excelforum.com/showthread...hreadid=473346

Trigger on leaving cell (of entire column)
 

First you need a way to track what column you have left, just like
rTriggerCell is used to track when you are in cell $A$1. So at the top of
the module, right above or under Dim rTriggerCell as Range, add a line saying:
Dim TriggerColumn as Integer
Now within the Worksheet_SelectionChange sub, add this block of code:

' See if both the prior selection and the new selection (Target) are in
column B:
If TriggerColumn = 2 And Target.Cells(1,1).Column=2 Then
MsgBox "Vertical move in column B"
End If
TriggerColumn = Target.Cells(1,1).Column

A couple notes:
- In one specific case this will fail: If the workbook is opened with the
selection in column B and the user moves to another cell in column B, this
first move will not be detected unless you add code to the Workbook_Open
procedure to set the value of TriggerColumn (you would need to make it a
Public variable to do this)
- There are ways to more efficiently combine this with the existing code,
but I want to keep it simple for you
--
- K Dales


"Lava" wrote:


The code below highlights the entire row I'm working on. When I leave
field $A$1 it popups a messagebox (just by example). I don't need to
make any changes to $A$1. All I have to do is leave it.

My question is.... what code should be replaced by what code to make it
work for every field in Column B? When the current cell is in column B
and I press enter or move the down-cursor (or up button) I wish the
trigger to execute. Basically vertical movement in column B should
trigger it. I assume something with the IF condition *If Target.Address
= "$A$1" Then* has to be changed, but what :) Trial and error so far.


Code:
--------------------
Dim rTriggerCell As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Triggers highlight of target row
Dim strRow As String
Cells.FormatConditions.Delete

With Target.EntireRow
strRow = .Address
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=COUNTA(" & strRow & ")0"

.FormatConditions(1).Font.Bold = True
.FormatConditions(1).Interior.ColorIndex = 34
End With

'Triggers an action upon user exiting cell A1
On Error Resume Next
Application.EnableEvents = False
'Entered into Trigger cell
If Target.Address = "$A$1" Then
Set rTriggerCell = Target
Application.EnableEvents = True
On Error GoTo 0
Exit Sub
End If

If Not rTriggerCell Is Nothing Then 'They are leaving A1
MsgBox "You just left cell A1", vbInformation, "OzGrid.com"
Set rTriggerCell = Nothing
End If
Application.EnableEvents = True
On Error GoTo 0
End Sub
--------------------


--
Lava
------------------------------------------------------------------------
Lava's Profile: http://www.excelforum.com/member.php...o&userid=27793
View this thread: http://www.excelforum.com/showthread...hreadid=473346