Macro to pop up a msg box with error

[gurtej176]

Hi All,

We do our reporting for different departments and we have a file which we send out. Each sheet has a error checking cells like A1:K1 that has conditional formatting which turn the cell colour to red if the total of accounts specified dont match the total account. Anyways, I don't need a macro to do this. But my problem is that we have 50 sheets in a workbook and maybe 1 has an error, so we have to check each individually to make sure that we have no error.

Is there any way we can have a macro in the control sheet that check each individual sheets and pop up a msg box displaying which sheets has errors. Please note that sheet names can change and we may add another sheet during the time.?

Any help will be greatly appreciated.

Thanks

 

[gurtej176]

Hi All,

I am writing macro for the first time and not very good in it. But I wrote the below macro for the above but i am sure i am missing something...can you please help

Sub Newmac()
For Each Sheet In Workbook
If Range(A1, k1) = "Error" Then
MsgBox ("Error in Worksheet")
ElseIf Range(A1, k1) = "Error" Then
MsgBox ("No error please distribute the sheet")
End If
Next
End Sub

it will also help me to understand..

 

[hockey1859]

Without knowing more specifics, such as what value displays in A1:K1 if there are errors, it's hard to write the macro specifically for your purposes, but this one should be fairly easily tailored to meet your needs:

Sub SheetErrors()

'Sets the variable ErrorSheets to a blank value
ErrorSheets = ""

'Sets up a loop to go through each sheet
For Sheetnum = 1 To Sheets.Count

'Selects the current sheet
Sheets(Sheetnum).Select

'Sets up a loop to go through column A through K
For ThisCol = 1 To 11

'Establishes an If to look for the value that determines an error
'Change the "Error" to whatever your error check value is
If Cells(1, ThisCol).Value = "Error" Then

'If there is an error, sets the ErrorCheck variable to "Error"
ErrorCheck = "Error"
End If
Next ThisCol

'If there was an error in column A through K, adds the sheet name to the list.
If ErrorCheck = "Error" Then
If ErrorSheets = "" Then
ErrorSheets = ActiveSheet.Name
Else
ErrorSheets = ErrorSheets & ", " & ActiveSheet.Name
End If
End If
ErrorCheck = ""
Next Sheetnum

'Msgbox to display which sheets have errors.
'Could also set it to populate a cell with the sheet names tha thave errors.
MsgBox ("Errors on sheet(s): " & ErrorSheets)
End Sub

Hope that works for you, if not let me know and we can tweak it.

 

[gurtej176]

Thanks for the above, its working great,,thanks for the comments that explain what codes are doing,,always of great help....