Msgbox to pop up only once considering multiple errors in column

[jharih1]

Good afternoon,

I looked through multiple posts on the forum boards, but something is not working properly with my code. I was hoping some of you VB experts can help me.

What I wish for it to do = look up errors in a column, highlight the erroneous cells red, and popup a msgbox once saying "Please enter valid month".

I created a public sub function to do the lookups..I think the code will speak to itself:

Private Sub Worksheet_Change(ByVal Target As Range)


'Checks if vlookup function returns an "ERROR" for all data validation and vendor number fields.
'If it is blank or has valid data, no conditional formatting will be used.
'If checks = "Error" then cell should be highlighted and a msgbox will popup requesting user to enter valid info.


Check_Month
End Sub

Public Sub Check_Month()
Dim Warning As Boolean


For i = 2 To 9999


    If Cells(i, 22).Value = "ERROR" Then
       Cells(i, 1).Interior.Color = RGB(198, 30, 2)
       MsgBox "Please enter valid Month"
       Warning = True
    Else:
       Cells(i, 1).Interior.ColorIndex = 0
       Warning = False
    End If
    
Next i


End Sub

Please help! I hate VB now

 

[Joe4]

Try this:

Public Sub Check_Month()
Dim Warning As Boolean

Warning = False

For i = 2 To 9999

    If Cells(i, 22).Value = "ERROR" Then
       Cells(i, 1).Interior.Color = RGB(198, 30, 2)
       Warning = True
    Else:
       Cells(i, 1).Interior.ColorIndex = 0
    End If
    
Next i

If Warning Then 
    MsgBox "Please enter valid Month"
End If

End Sub

 

[jharih1]

You are awesome! It works perfectly! Thank you so much

 

[Joe4]

You are welcome!

I hope the logic makes sense. You start off setting it to FALSE, and it only takes one to trigger it to TRUE (and you don't want to set it back to FALSE).
Then at the very end, outside of your loop, if the value is TRUE, it means that you found at least one, so show the MsgBox then.

Another similar method people will use is to count the number of errors (i.e ERRORS = ERRORS + 1), and then at the end, if that number is greater than zero, show the MsgBox.

 

[jharih1]

yes it made perfect sense. I was thinking of doing the count method too, but my spreadsheet is already too huge, I didn't want it to crash users' computers

 

[Joe4]

I was thinking of doing the count method too, but my spreadsheet is already too huge, I didn't want it to crash users' computers

It is just updating the value of a variable in VBA, so it shouldn't make much difference compared to the other way.
And while your spreadsheet may be huge, hopefully you won't have a "huge" number of errors!

If you are interested in making your code more efficient, trying to avoid loops whenever possible is a good strategy. There may be other ways to do what you want.
What exactly does your formula in column 22 look like?

 

[jharih1]

Its just a vlookup, which if #N/A returns an "Error" instead.

 

[Joe4]

Instead of coloring the cells in the VBA code, why not just use Conditional Formatting to highlight them (you can apply the Conditional Formatting by VBA code, if you like)?

And you can replace your loop and all the other code associated with the error count with one line like this:

Public Sub Check_Month()
    If Application.WorksheetFunction.CountIf(Range("N:N"), "Error") > 0 Then MsgBox "Please enter valid Month"
End Sub

 

[jharih1]

Problem is with conditional formatting - users were not "caring" and sending us invalid data as is. I hope that the msgbox + highlighting the cell with the invalid data will push importance on them to put in valid data.

That being said - there are more vlookups we are checking other than the Month. We feed this data into our Qlikview application to show captured savings. Sometimes they enter invalid vendor information, or geographical information. We do have data validations in place, but they have too many line items to enter, so they just end up copy/pasting information from their spreadsheets to this.

Do you think the new code you provided will do what I need them to do?

 

[Fluff]

Another option, without using CF is

Sub chk()

   With Columns(22)
      On Error GoTo allok
      .SpecialCells(xlFormulas, xlErrors).Offset(, -21).Interior.Color = RGB(198, 30, 2)
   End With
   MsgBox "Please enter valid Month"
allok:

End Sub

But for this to work, you'd have to change your Vlookup so that they return #N/A rather then "Error"