On Click Event?, and how to use

W

Wandering Mage

Is there a way for me to write a macro to have certain
cells toggle between X and "" every time they are
clicked? Does this need to be done on the "Module" level
if it is possible? I have never been able to find how I
write macros there. Help appreciated! :)
 
P

pikus

In the module for the worksheet in question, use th
Worksheet_SelectionChange event. Here is an example of how that can b
accomplished…

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

Piku
 
D

Don Guillett

How about just changing if you select the cell. No clicking necessary.
Right click sheet tab>view code>insert this. As written works on only column
A.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If UCase(Target) = "X" Then
Target = ""
Else
Target = "X"
End If
End Sub
 
T

thanks and more

Thanks guys, to both of you. Now further on the same
problem. First, the macro will error if you accidentally
select more than one cell. My project entails dummy
proofing the thing. Is there away to allow you to only
select one cell, or is there a way to make it swich the
multiple cells all at once (that would be really cool).
Second, I guess I would also prefer that it not actually
select the cell but just change it. Even some way to
insert something like:
returnto = ActiveWindow.RangeSelection.Address
Range(returnto).Select
would be good, but this particualar event calls out after
the cell(s) is/are already selected. Thanks again. Hope
you can help.
 
D

Don Guillett

1, insert this line as the FIRST line in the macro
on error resume next
2. The only way to change a cell without selecting is to pre-define it
range("a3").value="X"
 
P

pikus

For multiple cells you can do something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cel As Range
For Each cel In Target
If cel.Value <> "" Then
cel.Value = ""
Else
cel.Value = "X"
End If
Next cel
End Sub

If you only want certain cells to be affected you can put in another i
statement:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cel As Range
For Each cel In Target
If cel.Column <> 3 Then
If cel.Value <> "" Then
cel.Value = ""
Else
cel.Value = "X"
End If
End If
Next cel
End Sub

How's that? - Piku
 
Top