Hi all,
Question: Is it possible to set up an inputbox that would fix problems if the macro runs into an error?
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 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!
On ExcelForum I posted this question with an example workbook link: https://www.excelforum.com/excel-programming-vba-macros/1265455-fixing-errors-with-userform-while-macro-running-with-example.html#post5069775
Code in the example:
Could someone help me with this please?
Question: Is it possible to set up an inputbox that would fix problems if the macro runs into an error?
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 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!
On ExcelForum I posted this question with an example workbook link: https://www.excelforum.com/excel-programming-vba-macros/1265455-fixing-errors-with-userform-while-macro-running-with-example.html#post5069775
Code in the example:
Code:
Sub Error_correct_macro()
Dim i As Long
Dim CLUV As String
Dim CLUR As String
For i = 1 To Sheet2.ListObjects("DataTable").ListRows.Count
CLUV = Sheet2.ListObjects("DataTable").ListRows(i).Range(1, Sheet2.ListObjects("DataTable").ListColumns("Countries").Range.Column)
CLUR = WorksheetFunction.VLookup(CLUV, Sheet1.ListObjects("LookUpTable").DataBodyRange, 2, False)
If CLUR = "RED" Then
MsgBox "RED", vbOKOnly
Else
MsgBox "Not RED", vbOKOnly
End If
Next
End SubCould someone help me with this please?
