Is there a VBA Event to Detect Change in ActiveCell Focus?
 

I have an Excel worksheet that functions as a form of "X"ed items --
operating much the same as check boxes, but without using actual check box
controls. I would like to set an event that would trigger on clicking any
cell within a "watched" range -- which would then check to see what cell is
now active, and toggle "X" values on or off depending on what it finds in
the selected cell. Any thoughts??

Thanks in advance!! Larry.

Is there a VBA Event to Detect Change in ActiveCell Focus?
 

You would use the selectionchange event. Say you wanted to toggle
between X and nothing each time you selected a cell in column A other
than row 1 your event would look something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 And Target.Column = 1 And Target.Row 1 Then
If Target.Value = "X" Then
Target.Value = ""
Else
Target.Value = "X"
End If
End If
End Sub

This is worksheet event code. Right click the sheet tab, select View
Code and paste the code in there.

Hope this helps
Rowan

Larry A wrote:
I have an Excel worksheet that functions as a form of "X"ed items --
operating much the same as check boxes, but without using actual check box
controls. I would like to set an event that would trigger on clicking any
cell within a "watched" range -- which would then check to see what cell is
now active, and toggle "X" values on or off depending on what it finds in
the selected cell. Any thoughts??

Thanks in advance!! Larry.

Is there a VBA Event to Detect Change in ActiveCell Focus?
 

Looks very straightforward. I'll give a try. Thanks!

"Rowan" wrote in message
...
You would use the selectionchange event. Say you wanted to toggle between
X and nothing each time you selected a cell in column A other than row 1
your event would look something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 And Target.Column = 1 And Target.Row 1 Then
If Target.Value = "X" Then
Target.Value = ""
Else
Target.Value = "X"
End If
End If
End Sub

This is worksheet event code. Right click the sheet tab, select View Code
and paste the code in there.

Hope this helps
Rowan

Larry A wrote:
I have an Excel worksheet that functions as a form of "X"ed items --
operating much the same as check boxes, but without using actual check
box controls. I would like to set an event that would trigger on
clicking any cell within a "watched" range -- which would then check to
see what cell is now active, and toggle "X" values on or off depending on
what it finds in the selected cell. Any thoughts??

Thanks in advance!! Larry.

Is there a VBA Event to Detect Change in ActiveCell Focus?
 

This works but has one unexpected behavior. Once you click on a particular
cell, you cannot successively click to toggle back and forth. One must
first click on some other cell and then back again. But it's close. Any
other thoughts? Any way to create a behavior the same as a check box?
Thanks!!


"Rowan" wrote in message
...
You would use the selectionchange event. Say you wanted to toggle between
X and nothing each time you selected a cell in column A other than row 1
your event would look something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 And Target.Column = 1 And Target.Row 1 Then
If Target.Value = "X" Then
Target.Value = ""
Else
Target.Value = "X"
End If
End If
End Sub

This is worksheet event code. Right click the sheet tab, select View Code
and paste the code in there.

Hope this helps
Rowan

Larry A wrote:
I have an Excel worksheet that functions as a form of "X"ed items --
operating much the same as check boxes, but without using actual check
box controls. I would like to set an event that would trigger on
clicking any cell within a "watched" range -- which would then check to
see what cell is now active, and toggle "X" values on or off depending on
what it finds in the selected cell. Any thoughts??

Thanks in advance!! Larry.

Is there a VBA Event to Detect Change in ActiveCell Focus?
 

Hi Larry

The reason you have to click out of the cell and then back in is because
the selectionchange event only fires when the selected cell on the sheet
changes. There is no way around that as far as I know. Short of
replaciing the X's with checkboxes you could change the event slightly
so that it moves the selection to column B afer a cell is clicked eg:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
If Target.Count = 1 And Target.Column = 1 And Target.Row 1 Then
If Target.Value = "X" Then
Target.Value = ""
Else
Target.Value = "X"
End If
End If
Target.Offset(0, 1).Select
ErrorHandler:
Application.EnableEvents = True
End Sub

The user then only has to click once each time to change a cell.

Hope this helps
Rowan

Larry A wrote:
This works but has one unexpected behavior. Once you click on a particular
cell, you cannot successively click to toggle back and forth. One must
first click on some other cell and then back again. But it's close. Any
other thoughts? Any way to create a behavior the same as a check box?
Thanks!!