+ Reply to Thread
Results 1 to 9 of 9

Disable Run-Time Errors "Application.DisplayAlerts = False" Not Working

  1. #1
    Registered User
    Join Date
    04-11-2012
    Location
    Canada, Canada
    MS-Off Ver
    Excel 2003
    Posts
    2

    Disable Run-Time Errors "Application.DisplayAlerts = False" Not Working

    Long time lurker here, hoping to finally get some help on a lot of issues I'm hoping I did my due dilligence and have exhausted ever search possible, but if not I'm sorry in advance.

    The following code allows me to have a drop down list that fills in multiple fields once an option is selected. It works perfectly fine for fillig in one row at a time. However, if I try to fill down a row of information I get a run time error. This isn't an issue since everything gets filled down anyway, I just want to either prevent the error from poping up, or fix the code so there is no longer any error. Any help would be appreciated.

    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Disable Run-Time Errors "Application.DisplayAlerts = False" Not Working

    What error are you getting and where in your code?

    Application.Display alerts is working fine, it's just not intended to supress run-time error, so fixing the error would be the way to go.

    Probably not the bit giving you an error, but I'd avoid doing this:

    Please Login or Register  to view this content.
    It would be neater to just go with:

    Please Login or Register  to view this content.

  3. #3
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Disable Run-Time Errors "Application.DisplayAlerts = False" Not Working

    Another note,

    When dealing with worksheet change events (or selection change events), it is best to also check the target.count. If a user selects more than one cell and does a paste or a clear or types a value and hits CTRL+ENTER, when you try to set FindMe to Target.Value, you'll get an error.

    And using AndrewR's code will probably get rid of your error. Since you are using a With Rng, and then you change the Rng to the results of a search, I bet this would cause issues.

    As far as error trapping is concerned, I would recommend reading up on proper methods. If there is even the slightest chance your code will produce an error, it is usually best to trap and handle the error, rather than just throw Run-Time errors at the user. Even a simple Catch-All of "On Error Goto ErrHandler" at the beginning of the code and then a MsgBox to display the error number and description can make things less alarming to the user.

    http://www.cpearson.com/excel/errorhandling.htm

  4. #4
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Disable Run-Time Errors "Application.DisplayAlerts = False" Not Working

    Upon testing your code as is, I am not getting an error unless, as I said, I have multiple cells selected when the change occurs.

    However, looping will solve this issue:

    Please Login or Register  to view this content.
    Last edited by Whizbang; 04-12-2012 at 10:22 AM.

  5. #5
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Disable Run-Time Errors "Application.DisplayAlerts = False" Not Working

    After playing with it some more, the looping solution needed some work to account for the fact that the user could select multiple columns that might include column 30.

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    04-11-2012
    Location
    Canada, Canada
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Disable Run-Time Errors "Application.DisplayAlerts = False" Not Working

    Andrew-R / Whizbang,

    You guys are amazing, thank you so much. It all works perfectly now I have much to learn.

  7. #7
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Disable Run-Time Errors "Application.DisplayAlerts = False" Not Working

    All kudos goes to Whizbang, all I did was moan about a minor bit of coding

    Glad you're sorted, though.

  8. #8
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Disable Run-Time Errors "Application.DisplayAlerts = False" Not Working

    Quote Originally Posted by Whizbang View Post
    And using AndrewR's code will probably get rid of your error. Since you are using a With Rng, and then you change the Rng to the results of a search, I bet this would cause issues.
    Sorry, I know this one is solved, but I just wanted to add that I was fairly sure that although the bit of code I pointed out grates a bit I don't think it actually produces an error.

    I was fairly sure, from something I'd tried in the past, that when you start a With block Excel remembers the object and you can't change it within the With. This little bit of code shows that Excel does track these things separately:

    Please Login or Register  to view this content.

  9. #9
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Disable Run-Time Errors "Application.DisplayAlerts = False" Not Working

    Yeah, I was a little shocked when I ran his original code and didn't get an error or have some wierd results. Still, having the same variable perform two different functions at the same time will just lead to confusion.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1