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.

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.

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

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.

Andrew-R / Whizbang,

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

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

Glad you're sorted, though.

Originally Posted by Whizbang

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.

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.