I like Doug Glancy’s UndoSelections code via his Selectracker utility. Nifty.
I thought I’d try a simpler approach…simply not let the user select a cell that’s already selected. So I came up with this:
Set App = Application
End Sub
Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Deselect Target
End Sub
Sub Deselect(Target As Range)
Dim lngCount As Long
Dim lngLast As Long
Dim strTarget As String
Dim strOld As String
Dim strNew As String
‘This code allows you to deselect cells when CTRL + Clicking
strTarget = Target.Address
lngCount = UBound(Split(strTarget, ","))
If lngCount > 0 Then
strNew = Split(strTarget, ",")(lngCount) & ","
‘Need to add the "," as a delimiter so we don’t incorrectly identify say $A$1 and $A$10 as the same
strOld = Left(strTarget, Len(strTarget) – Len(strNew)) & ","
If InStr(strOld, strNew) > 0 Then
If strOld <> strNew Then
strOld = Replace(strOld, strNew, "")
End If
If Right(strOld, 1) = "," Then strOld = Left(strOld, Len(strOld) – 1)
Application.EnableEvents = False
Range(strOld).Select
Range(Split(strOld, ",")(UBound(Split(strOld, ",")))).Activate
Application.EnableEvents = True
End If
End If
End Sub
Put this code in your Personal Macro Workbook, then save. Next time you open Execl, it will fire the Workbook_Open event which will capture application-level events, so this will be available everywhere.
It works pretty well. See for yourself: hold Ctrl down and do some crazy clicking, and occasionally click something you’ve already selected.
ZAP!
Why this isn’t the native behavior right out of the box is beyond me.
There’s bound to be coding improvements, so let’s have ‘em.
