Creating inputbox code to fix errors while macro running? (With example)

Hi all,

Example below.

Question: Is it possible to set up a userform that would fix problems if the macro runs into an error?

In the example I have Countries in my DataTable and I have Countries in the LookUpTable, with a colour assigned to the Countries, and I have a code, which loops through each row in the DataTable, and tells me if the Country is "RED" or "Not RED". The only problem is, that in the DataTable, I have blanks, and some users input city names instead of countries, or abbreviations.

So would it be possible, to fix it while my macro is running with a userform? It hopefully would be able to do 2 things:

First example: Macro runs into Spain, which is not in my LookUpTable, and I would like the userform to pop up saying:

"Spain is not in the LookUpTable, do you want to change the cell in the DataTable?" Corrected country name: (Inputbox here) YES / NO

If the user inputs something in the Inputbox and clicks yes, then change the cell value to the inputbox value, and resume the macro checking if the country name that has just been put in is "RED" or "Not RED"

If the user clicks no, then stop the macro, so the user can go back to the LookUpTable, and add the country that caused the error, to the LookUpTable.


For the blanks the same userform should pop up and the user can decide to input a country name, or just write "BLANK" which is also in the LookUpTable.

This is a VBA project, and I need to solve it with VBA, so please don't waste your own time solving the problem with formulas!

Could someone help me with this please?

Question posted on Mr.Excel Message Board as well link: https://www.mrexcel.com/forum/excel-...ml#post5230254

There is no Userform here. Please Amend your Title.

I have a code that may solve your problem, but as requested please amend your title... as mentioned this does not have a userform involved.

Ok, given your description, the title does work, I just didn't understand you were asking for a form (apologies)... but my suggestion is not to use a userform since we can keep it much simpler. If you truly DO want a userform we can make the necessary changes. In the meantime, see if this code will satisfy your needs?

Please Login or Register  to view this content.

No it is just some message boxes and input boxes, but not a userform

Glad I could help, if your issue is resolved please remember to mark the thread as solved? Thanks!

I'll look at it soon... My assumption was if they answer yes to the first question, then we should update the cell no matter what.

Sorry for the delay, this would take care of the Cancel button or the X at the top of the box being used. If the user did that program stops as if they had said No to the question about updating. If they just left it blank and hit ok then "BLANK" would be entered. I gather you don't need it anymore, but thought I'd post just in case.

Please Login or Register  to view this content.

The issue with "FALSE" was probably because you need to make sure to handle the case where StrPtr(new_country) is 0. Either make new_country = vbNullString or exit the sub as I did in my code. That said, if you have already solved it in another way, then great, and this probably doesn't matter anymore. Thanks for the reply and feedback though