Enter Text in Cell on Click

[Steven Drenker]

I want to be able to do the following:
1. Click in a cell inside a named range ("DNS_zones_selection") and have "x"
entered into the clicked cell.
2. If the cell already has an entry, it should be erased.
3. You should be able to toggle the cell contents on and off by repeatedly
clicking on the same cell without clicking another cell.

The following code meets #1 and #2 above as long as you click in the target
cell, then click outside the target cell, then click back in the target
cell.

Is there a way to achieve all three goals without having to first click
outside the range?


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If InRange(Target, Range("DNS_zones_selection")) Then
Select Case Target.Text
Case ""
Target.Value = "x"
Case Else
Target.Value = ""
End Select
End If
End Sub

Private Function InRange(rng1, rng2) As Boolean ' courtesy John Walkenbach,
"Excel 2000 Power Programming," Ch. 11, "in range.xls"
' Returns True if rng1 is a subset of rng2
InRange = False
If rng1.Parent.Parent.Name = rng2.Parent.Parent.Name Then
If rng1.Parent.Name = rng2.Parent.Name Then
If Union(rng1, rng2).Address = rng2.Address Then
InRange = True
End If
End If
End If
End Function

 

[Dave Peterson]

Have you thought about using the Worksheet_BeforeDoubleClick or
Worksheet_BeforeRightClick. You could use one to toggle on/off or you could use
both--one to put the X and one to clear the X.

 

[Tom Ogilvy]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng as Range
set rng = Intersect(Target,Range("DNS_zones_selection"))
If Not rng is Nothing Then

Select Case rng(1).Text
Case ""
rng(1).Value = "x"
Case Else
rng(1).Value = ""
End Select
Application.EnableEvents = False
rng(1).offset(0,1).Select
Application.EnableEvents = True
End If
End Sub