Help with a worksheet_selectionChange

Microsoft Excel 97 upgrade
February 2, 2011 at 19:23:19
Specs: Windows 7
Hi, I have this simple code for a sheet in an excel document that I can't fully figure out. I am trying to get a bullet to appear in a cell when the cell is selected and I would like the bullet to disappear if the cell is selected again. I have it close but it's not exactly how I would like it. I would like to be able to click a cell have the bullet appear and then click the same cell again immediately and have the bullet disappear. Here is the code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim targetRange As Range, selectedRange As Range, col As Range
Set targetRange = Range("A1:vG15")
Set selectedRange = Intersect(targetRange, Target.Cells(1))
If selectedRange Is Nothing Then _
Set targetRange = Range("A30:G45")
Set selectedRange = Intersect(targetRange, Target.Cells(1))
If selectedRange Is Nothing Then _
Exit Sub

If selectedRange.Cells(1) = "•" Then
selectedRange.Cells(1) = ""
Exit Sub
End If

selectedRange.Cells(1) = "•"
End Sub

Thanks for any help.

-John


See More: Help with a worksheet_selectionChange

Report •

#1
February 2, 2011 at 20:54:51
re: "then click the same cell again immediately"

Note the Name of the sub:

Worksheet_SelectionChange

If you select the same cell immediately, you haven't changed the Selection.

You'll need to select another cell before re-selecting the cell you just bulleted, perhaps with something like this:

       Application.EnableEvents = False
         Target.Offset(1, 0).Activate
       Application.EnableEvents = True

BTW, this also seems to do what you want, and is a bit shorter.:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Selection.Cells.Count = 1 Then
  If Not Intersect(Target, Range("A1:vG15")) Is Nothing Or _
     Not Intersect(Target, Range("A30:G45")) Is Nothing Then
      If Target = "•" Then Target = "" Else Target = "•"
       Application.EnableEvents = False
        Target.Offset(1, 0).Activate
       Application.EnableEvents = True
  End If
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question