How can I fix this macro to work properly?

How can I fix this macro for it to work properly?

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim cell1 As Range
Dim cell2 As Range

For Each cell1 In Sheets("FABRICATION WORK ORDER").Range("C1:C6,C9:C16,E28")
If cell1.Value = "" Then
Cancel = True
MsgBox "Cell " & cell1.Address & " is empty - Please fill the cell before saving!", vbOKOnly, "Check Cells"
Exit Sub
End If
Next cell1
For Each cell2 In Sheets("INSTALLATION WORK ORDER").Range("D9:D15,D19:D22,I12:I19")
If cell2.Value = "" Then
Cancel = True
MsgBox "Cell " & cell2.Address & " is empty - Please fill the cell before saving!", vbOKOnly, "Check Cells"
Exit Sub
End If


Next cell2

End Sub


Private Sub Workbook_Open()

End Sub

I would like the error to look like "Cell C1 is empty - Please fill the cell before saving!" instead of the way it's show in the image below.

Warning Message.jpg


Thank you so much!

Please wrap your code in code tags.

You can use the following code to get what you are looking for:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Regrds, TMS

I tried that and it didn't work.

Tried what and it didn't work?

Please Login or Register  to view this content.

Regards, TMS

You're welcome.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save