I have a situation where I want to cycle through several values in a cell and my code is in the Worksheet_SelectionChange event. I want it to behave like a checkbox in that I can repeatedly change it's value by clicking it. My values however are in a cell and to simulate a click you need to leave the cell and come back to it. My solution to that was after the cell is selected and changed (via code) I would turn off ScreenUpdating, select a cell via code that was off-screen, and then turn screen updating back on. That works fine except if I "click"" the cell too fast. When that happens the off-screen cell is selected but I'm left there. Is there any way to prevent that?
Here's the code. It affects A1 to A5.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ErrorRoutine
If Target.Cells.Count = 1 Then
' Cycle between checkmark, ! and X
If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
Select Case Asc(Target)
Case 80 ' It's a checkmark so change to !
With Target.Font
.Name = "Calibri"
.FontStyle = "Bold"
End With
Target = "!"
Case 33 ' it's an ! so change it to X
With Target.Font
.Name = "Calibri"
.FontStyle = "Bold"
End With
Target = "X"
Case 88 ' It's an X so change it to a checkmark
With Target.Font
.Name = "Wingdings 2"
.FontStyle = "Bold"
End With
Target = "P" ' This is a checkmark in the Wingding 2's font
End Select
Application.ScreenUpdating = False
Target.Offset(0, 999).Select
Application.ScreenUpdating = True
End If
End If
Exit Sub
ErrorRoutine:
If Err.Number = 5 Then
' The cell is blank so make it a checkbox
With Target.Font
.Name = "Wingdings 2"
.FontStyle = "Bold"
End With
Target = "P" ' This is a checkmark in the Wingding 2's font
Application.ScreenUpdating = False
Target.Offset(0, 999).Select
Application.ScreenUpdating = True
End If
End Sub