macro for "entering data"

[Jolly Rogers]

Can anyone suggest a good macro to allow me to select (touch) any cell in
a given range, and have the alpha character "X" (capitalized) appear in
the cell when the cell is selected?

For example, let's consider cell A1. A1 is initially blank and void of
any formulas. If I select A1 with my mouse, I want "X" to appear in A1.
If I select A1 again, I want X to disappear from A1, and so forth and so
on.

(Note: For my purposes, I prefer not to use a checkbox or radio buttons
because I already have conditional formatting depending on the contents of
the cells which will either have X or not.)

Thanks much!

Joseph

 

[papou]

Hi Joseph
May be this could do?
Right-click on worksheet tab, choose View Code and paste the following:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
If Target.Value = "" Then
Target.Value = "X"
Else: Target.Value = "": End If
End Sub

PS: this will only work when the cell is actually selected (either with left
click from mouse or with keyboard arrow)

HTH
Cordially
Pascal

 

[Jolly Rogers]

That works beautifully. Thanks.

How could I do the same thing for individual cells in a range though?
Say, anytime a cell in range $A1:$A100 is selected, it toggles on (or off)
with "X"?

Thanks again.

Joseph

 

[Gord Dibben]

Joseph

Try this variation.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
With Target
If Target.Value = "" Then
Target.Value = "X"
Else: Target.Value = ""
End If
End With
End If
End Sub


Gord Dibben Excel MVP

 

[Jolly Rogers]

Perfect. Works flawlessly. Thanks much, Gord.

Joseph