Deselecting cells while multi-selecting

[BillB]

I've struggeled with this for too long.

When I multi-select cells (in a worksheet or pivot) using
CTRL+Click or SHIFT+Click, I sometimes select an incorrect
cell. My efforts to de-select the incorrect cell have
proven futile. No matter what I do, I wind up simply
having to release the CTRL or SHIFT key and click
somewhere random to deselect everything and then start
over. Surely there's a better way.

According to the "Select objects" section of Excel Help:
"To deselect one object at a time, hold down SHIFT and
click the object."
But that doesn't help (with or without the CTRL key still
down). Holding SHIFT and clicking the cell either
deselects everything or acts like an anchor and selects
everything from my previous click through to the cell I
want to deselect (and it remains selected).

Anyone have a suggestion?

Example:

Hold the CTRL key down while you click these cells:
A3
A4
A5
A9
A10

Now try to deselect A4.

Thanks in advance,

BillB

[Chip Pearson]

Bill,

I long ago got tired of the situation you describe, and wrote two
procedures to deal with it. The first, UnSelectActiveCell removes
the active cell from the selection. The second, UnSelectActiveArea,
removes an entire area from the selection. Attach these procedures
to your right-click menu and you'll be all set.

Sub UnSelectActiveCell()
Dim Rng As Range
Dim FullRange As Range

If Selection.Cells.Count > 1 Then
For Each Rng In Selection.Cells
If Rng.address <> ActiveCell.address Then
If FullRange Is Nothing Then
Set FullRange = Rng
Else
Set FullRange = Application.Union(FullRange, Rng)
End If
End If
Next Rng

If FullRange.Cells.Count > 0 Then
FullRange.Select
End If
End If

End Sub
'-------------------------------
Sub UnSelectActiveArea()

Dim Rng As Range
Dim FullRange As Range
Dim Ndx As Integer
If Selection.Areas.Count > 1 Then
For Each Rng In Selection.Areas
If Application.Intersect(ActiveCell, Rng) Is Nothing Then
If FullRange Is Nothing Then
Set FullRange = Rng
Else
Set FullRange = Application.Union(FullRange, Rng)
End If
End If
Next Rng
FullRange.Select
End If

End Sub
'-------------------------------

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"BillB" <bil...@spro.net> wrote in message
news:063c01c302d1$8a6aeeb0$2f01...@phx.gbl...

[BillB]

Thanks for the suggestion and the code, Chip! Now I need
help getting that attached to my right-click menu (I'm a
bit of a neophyt for programming Excel). Can you walk me
through that process or point me to docs that will?

Also note: I'm required to run at Medium security level,
and most people I work with have their setting to disallow
running macros (I know, it takes away so much from the
power of Excel, but it's policy...). The best solution
would allow me to use this on any spreadsheet I was
working on without being included in the file when I share
it with others. Is that possible?

BillBR

>.
>

[Anders S]

First of all, thank you for the fix to this annoyning situation. One of
these things one learns to live with, but never accept.

What really annoys me now is that I cant't find out how to modify the
"right-click menu". I have examined almost every item in Tools/Customize
three times over but I can't find anything that looks like the "right-click
menu". Where else can it be?

I have a Swedish version of Excel 2002, but it should be good enough if you
tell me in English how to do.

Best regards,
Anders Silven

"Chip Pearson" <ch...@cpearson.com> skrev i meddelandet
news:u#KVxTtAD...@TK2MSFTNGP10.phx.gbl...

[Chip Pearson]

Anders,

You have to do it with code. Put the following in the Workbook_Open
procedure of your personal.xls file.

With Application.CommandBars("Cell").Controls
With .Add(temporary:=True)
.Caption = "Unselect Active Cell"
.OnAction = ThisWorkbook.Name & "!UnSelectActiveCell"
.BeginGroup = True
End With
With .Add(temporary:=True)
.Caption = "Unselect Active Area"
.OnAction = ThisWorkbook.Name & "!UnSelectActiveArea"
End With
End With

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"Anders S" <anders...@hotmailxyz.com> wrote in message
news:eA3hRHuA...@TK2MSFTNGP11.phx.gbl...

[Anders S]

Chip,

I have to try this out tomorrow - 3AM here.

Funny bird, this CommandBars("Cell").

Once again, thank's a lot.

Anders Silven

"Chip Pearson" <ch...@cpearson.com> skrev i meddelandet

news:#yUSqTuA...@TK2MSFTNGP10.phx.gbl...

[BillB]

I'm not finding a personal.xls file on my system. I do see
an excel.xls in the the Template folders in my Documents
and Settings folder (I'm running Windows 2000 Server). Is
that the file you're referring to?

I'm also not sure how to put the code in the
Workbook_Open. Is there more complete documentation that
takes one through adding code like this to a spreadsheet?

Thanks,

BillB

>.
>