PDA

View Full Version : [SOLVED] Help with Error Message returning the Row and Sheet that contains the error



dmarchina
05-25-2015, 08:41 AM
Hi all.

I have a code to check in all sheets of a Workbook if in the range AL:12:AL1001 of each sheet is there any cell with Error value. On those ranges there is a formula in each row that check if any mandatory field is blank, and if yes the formula returns an error message. The code works just fine, but I was wondering if it is possible to include in the error message that the code returns the information in which Sheet and in which row the error was identified. Something like:

"Please enter a value in all mandatory fields for each filled line, check row 30 from Sheet1"

Below is the code:


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


Dim sht As Worksheet
Dim mycell As Range


For Each sht In ThisWorkbook.Worksheets
For Each mycell In sht.Range("AL12:AL1001")

If mycell.Value = "Error" Then
Cancel = True
Response = MsgBox("Please enter a value in all mandatory fields for each filled line", vbCritical, "Error!")

End If

Next mycell
Next sht

End Sub



Thanks in advance, and sorry about any problems with my English.

p45cal
05-25-2015, 12:28 PM
Response = MsgBox("Please enter a value in all mandatory fields for each filled line" & vblf & "check row " & my cell.Row & " in sheet " & sheet.name , vbCritical, "Error!")

dmarchina
05-25-2015, 01:22 PM
Thanks man, it worked with the code below:

Response = MsgBox("Please enter a value in all mandatory fields for each filled line" & vbLf & ", check row " & mycell.Row & " in sheet " & sht.Name, vbCritical, "Error!")

p45cal
05-26-2015, 09:28 AM
Yes, I was replying on a 'phone with predictive text and I hadn't noticed it's 'corrections'!