Undo Selections With SelecTracker!

Undo Selections With SelecTracker!

On one of the DDOE posts about International Keyboard Shortcut Day, a commenter known only as VvM asked for ways to undo selections in Excel. You know how it goes. You’ve selected various parts of a huge table to turn them bold or purple or something and you grab one more cell than you wanted. Wouldn’t it be great if you could just unselect it by clicking again, the way you can in just about any other #$;@#%! situation you can imagine. So, you keep clicking and it kind of looks like it worked because the active cell is a different color than the rest of the selection, but no. You’re doomed! You’ve got to start over from scratch. Aargh!

Anyways, the answer is there’s no way to undo selections in the Excel interface proper. You need VBA. And VBA is what you get in the handy form of my newest utility, SelecTracker.

Imagine you’re entered in a contest where you only have to click three cells with high dollar amounts to walk away with fistfuls of cash. Once you start you can’t start over, but you are allowed to use free utilities with goofy names that you downloaded from Excel websites (also with goofy names). In that case SelecTracker could really save your bacon:

That’s right. Just uncheck the offending selection(s) in the handy list and it’s like it never happened.

Top three gifts

Other lovely SelecTracker features:

– Start it after your selections are made and you realize you need to unselect
– Doesn’t affect Excel’s Undo stack
– Gives you a better understanding of how Excel selections work

Weird Things I Learned About Selecting in Excel

Try this:

Hold down the Ctrl key and click A1 five times.

Now, in the Immediate window type:
? Selection.Areas.Count and hit Enter

Now enter:
? Selection.Address

immediate window #1

So, all those times you tried to unselect by clicking again, you were really just selecting it even more. If only you’d had SelecTracker back then:

A1 many times

If you hold down the Ctrl key and click in cells A1 to A5 one at a time, you’ll get similar, slightly less weird, results. And if you do it and then start up SelecTracker (or vice-versa), you’ll see a different representation of the same results:

SelecTracker_1

So, each selection (with a small “s”) in a Selection is a VBA Area. On the linked MSDN page the Areas definition reads:

The Areas collection contains one Range object for each discrete, contiguous range of cells within the selection.

But clearly that’s within the VBA world where Range("A1:A5") has one Area. But within a Selection (with a capital “S”) it has one Area for each selection you make while holding down the Ctrl key. “Contiguous” doesn’t really enter into it.

This makes the VBA pretty simple. To populate the form’s listbox, I just cycle through the Selection’s Areas and add each one:

With Me.lstSelections
    .Clear
    For Each SelArea In Selection.Areas
        .AddItem SelArea.Address
        .Selected(.ListCount - 1) = True
    Next SelArea
End With

And to re-select the areas when a listbox item is checked or unchecked:

With Me.lstSelections
    For i = 0 To .ListCount - 1
        If .Selected(i) Then
            NewSelection = NewSelection & .List(i) & ","
        End If
    Next i
    NewSelection = Left(NewSelection, Len(NewSelection) - 1)
    ws.Activate
    ws.Range(NewSelection).Select
End With

I thought I’d do the above by Unioning the various Areas represented in the listbox. But once you Union them they’re not Areas any more. At least not if there contiguous. So instead I glued the text from the list items together.

(I don’t think other countries use other delimiters than commas, but can’t remember for sure and had no luck searching for it. Please tell me if you know otherwise.)

The rest of the code uses some concepts from UserForm Application-Level Events, Chip Pearson’s code for making forms resizable, and other neat stuff.

Download it and see for yourself.

Thanks VvM, whoever you are!

8 thoughts on “Undo Selections With SelecTracker!

    • I think Excel’s behavior is odd, but I suppose if people use this tool they’ll figure it out and change what they expect. Plus I like the cleanness of the code and if I try to obscure what’s really happening, and then have to code for when they de-select, etc., it will get all complicated and be more like work 🙂 instead of this fun little behind-the-scenes quirk this tool brings to light..

      Pretty deep, huh?

  1. Pingback: Excel Roundup 20141110 « Contextures Blog

  2. Pingback: Daily Dose of Excel » Blog Archive » No you CANNOT have more of the same

  3. I like how you do this with Events.

    I made something long ago using an input box (Application.InputBox) and will try to post here. The funny thing is the Autofilter stuff. Can’t remember why it’s there but I must have had a reason.

    Here goes.

    Private Sub DeselectCellsWithinRange()
        Dim rCell As Range
        Dim CurrentRange As Range
        Dim DeselectRange As Range
        Dim NewRange As Range
        Dim FilterRange As Range
       
        On Error Resume Next
       
        Set CurrentRange = Selection
       
        Set DeselectRange = Application.InputBox("Choose the cells that you want to deselect." _
        & vbNewLine & vbNewLine & "Keep the Ctrl key depressed to select multiple areas.", _
        "Deselect Cells Within Range", , , , , Type:=8)
       
        Set FilterRange = ActiveSheet.AutoFilter.Range
       
        ActiveSheet.AutoFilterMode = False
       
        For Each rCell In CurrentRange.Cells
           
            If Intersect(rCell, DeselectRange) Is Nothing Then
               
                If NewRange Is Nothing Then
                   
                    Set NewRange = rCell
                   
                Else
                   
                    Set NewRange = Union(NewRange, rCell)
                   
                End If
               
            End If
           
        Next rCell
       
       
        FilterRange.AutoFilter
       
        NewRange.Select
       
        Set CurrentRange = Nothing
       
        Set DeselectRange = Nothing
       
        Set FilterRange = Nothing
       
        Set NewRange = Nothing
       
        On Error GoTo 0
    End Sub

Leave a Reply to Andrew Engwirda Cancel reply

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>