Error Handling - make macro more user friendly

[jtn260]

Hi,

Thank you so much to all who have helped me out so far. I currently have a macro that allows the user to input numbers that will populate an excel sheet. The only problem is when they spell something wrong or enter something in the wrong format the macro will crash and give them an error. Basically I'm trying to program that if an error occurs, allow the user to repeat the last step and input a new value. I've attached my workbook and code below. I'm still relatively new to VBA, any help would be appreciated. Thank you FindDateBoxMacro.xlsm

Please Login or Register  to view this content.

[Norie]

To take care of the error you need to check if RowIDX and ColumnIDX have values before you ask for user input here.

Please Login or Register  to view this content.

You could do that with this.

Please Login or Register  to view this content.

As for repeating the last step, what step(s) do you actually mean?

[protonLeah]

The month popup should be inside the DO loop:

Please Login or Register  to view this content.

[jtn260]

Thanks for the replies guys. What I meant was for the line

Please Login or Register  to view this content.

If I leave it blank, type in letters/words or type in a box number that is not there; The macro will continue to run to the end and give an error because it won't have the proper indexes. Most of the time this won't be a problem, but time to time I expect the user might mistype something and I don't want the macro to crash.

Basically I want to check that for every input; the macro will know what to perform for all cases such as:
If the user accidently leaves the BxNumber empty, the macro crashes.
If the user mistypes a date like 5/1/14 in the April tab, the value won't be inputted because it can't find the date; the macro will crash

You guys have been a great help so far! Thanks so much!

[jaslake]

Hi jtn260

Try this...you might want to consider a User Form...

Please Login or Register  to view this content.

[protonLeah]

What's the range of box numbers, i.e., are there any less than 1000 or more than 9999?

[jtn260]

Thanks guys. I'll try your code out jaslake. To protonLeah, the range varies between 1000 - 35000.

[protonLeah]

Please Login or Register  to view this content.