Disable Alert Display? (2002)

[GreggBeck]

When a range is selected next to other filled cells and a Sort command is
launched, an alert appears asking to expand the selection to the current
region or continue with the selected range. I appreciate this safeguard for
beginners, but I would rather disable it. I've found no such option, so
tried making new Sort buttons that included code to cancel the alert:

Application.DisplayAlerts = False
Selection.Sort Key1:=Range(ActiveCell.Address), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Yet the alert still appears. Is what I want possible, or is my code wrong?

Thanks for your time and help.

[sdckapr]

Your macro works with no Alerts in Excel97:The selected range is sorted with no alerts even though if I use the excel sort button the alert is there. It could be an added feature in later versions

I did have to get rid of the DataOption1 parameter. It must exist in later versions of excel.

Steve

[GreggBeck]

Thanks, but this can only be tested in version 2002, as 97 didn't have the alert I'm referring to.

Gregg

[pieterse]

You might try using

Selection.CurrentRegion.Sort .......

And change
Header:=xlGuess
to
Header:=xlYes

(If you always have a header)

[GreggBeck]

Thanks for the input, but the current region is what I don't want. That's the exact redundancy of the alert. It asks if you want to expand to the current region. I want sorted just what I have selected only (as it would do in 97). If I want the current region, I would select that.

And the header has to guess as there will not always be one.

Point is... MS put in a safeguard for beginners who may not realize what they have selected when they sort. For experienced people, however, it's a pain to tell the program each time, "Yes, that's what I want." I can turn off other alerts, such as overwrite, and even defeat other alerts with code.

Has anyone found a way to defeat this particular alert?? Does anyone know the exact alert I'm talking about? It's in version 2002.

Gregg

[rory]

Hi Gregg,
I just ran your code in XL2002 and got no alert - it just did the sort. My test was on one column of data - are you using multiple columns?

[GreggBeck]

The alert only appears when other filled cells are next to your selection. Make a table. Select a range of cells within that table. Hit the sort button. The alert will appear, asking if you want the whole current region or just the cells you originally selected.

Even with my code, the alert showed up. See if it does with you.

Gregg

[rory]

Nope - no alert. I do get it if I just click the sort button, but not if I run your code.

[GreggBeck]

That's odd! Suddenly it works for me too, now! No telling what went wrong the first times. I'll bet, in making the new buttons for the toolbar, I inadvertenly used the normal sort buttons instead of my new buttons.

Thanks for the confirmation which made me go back and try the code again to see it works.

Gregg

[pieterse]

HI,

<<but the current region is what I don't want. That's the exact redundancy of the alert. It asks if you want to expand to the current region. I want sorted just what I have selected only (as it would do in 97). If I want the current region, I would select that>>

Sorry for misunderstanding. This sure sounds like a bug to me.

Does pressing the sort button(s) on the standard toolbar (the ones with A and Z above each other) issue the same warning message? (they don't on XL2K, where-as the Data, Sort menu item does give the warning screen you mentioned).

[GreggBeck]

<hr>Does pressing the sort button on the standard toolbar issue the same warning<hr>

Yes. In fact, those are the only sort tools I use. I never use the sort dialog box. But another person prodded me to re-try my code and to my surprise it works. I thought my code didn't work when I first tried it, but it does. It disables the alert and sorts what ever I have selected. It saves me two clicks.

Gregg

[pieterse]

Good.
Another mystery solved.

[zeddy]

Have you tried assigning a temporary name to the range you want sorted?
Also, when I want to do something like this I usually insert a temp column to 'break' the current region and then remove the temp column after the sort.

zeddy

[GreggBeck]

Thanks, but no need to all that. Simply make new sort buttons with the code that replaces the old sort buttons. Then the sort warning doesn't show.

Gregg