Msgbox to pop up only once considering multiple errors in column

jharih1

New Member
Joined
Dec 5, 2017
Messages
14
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:
Code:
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
Code:
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 :(
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,948
Office Version
  1. 365
Platform
  1. Windows
Try this:
Code:
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,948
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 5, 2017
Messages
14

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,948
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 5, 2017
Messages
14

ADVERTISEMENT

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,948
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
Public Sub Check_Month()
    If Application.WorksheetFunction.CountIf(Range("N:N"), "Error") > 0 Then MsgBox "Please enter valid Month"
End Sub
 
Last edited:

jharih1

New Member
Joined
Dec 5, 2017
Messages
14
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,084
Office Version
  1. 365
Platform
  1. Windows
Another option, without using CF is
Code:
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"
 

Watch MrExcel Video

Forum statistics

Threads
1,122,865
Messages
5,598,539
Members
414,246
Latest member
allyciv

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top