Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Disable Alert Display? (2002)

    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.

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts

    Re: Disable Alert Display? (2002)

    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

  4. #3
    New Lounger
    Join Date
    Sep 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disable Alert Display? (2002)

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

    Gregg

  5. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disable Alert Display? (2002)

    You might try using

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

    And change
    Header:=xlGuess
    to
    Header:=xlYes

    (If you always have a header)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #5
    New Lounger
    Join Date
    Sep 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disable Alert Display? (2002)

    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

  7. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,896
    Thanks
    0
    Thanked 86 Times in 82 Posts

    Re: Disable Alert Display? (2002)

    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?
    Regards,
    Rory
    Microsoft MVP - Excel.

  8. #7
    New Lounger
    Join Date
    Sep 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disable Alert Display? (2002)

    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

  9. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,896
    Thanks
    0
    Thanked 86 Times in 82 Posts

    Re: Disable Alert Display? (2002)

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

  10. #9
    New Lounger
    Join Date
    Sep 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disable Alert Display? (2002)

    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

  11. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disable Alert Display? (2002)

    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).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #11
    New Lounger
    Join Date
    Sep 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disable Alert Display? (2002)

    <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

  13. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disable Alert Display? (2002)

    Good.
    Another mystery solved.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  14. #13
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,494
    Thanks
    28
    Thanked 171 Times in 167 Posts

    Re: Disable Alert Display? (2002)

    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

  15. #14
    New Lounger
    Join Date
    Sep 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disable Alert Display? (2002)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •