Can someone help me with my coding to return error box only one time and also to create msgbox as Critical?

[KristenB]

Hello, I am attempting to create an error message msgbox in an Excel workbook/worksheet. I have the coding to create the msgbox but if the error is on the worksheet multiple times, it shows the error multiple times. I have tried using alreadyprompted coding to stop this action but I'm having no luck. Additionally, I would like the error message to show a 'critical' icon on the error message and that is also not working. I would appreciate any help you can give. Here is the coding that I have so far:


Private Sub ProjNumbrReq()


Static alreadyPrompted As Boolean
If alreadyPrompted Then Exit Sub

Dim myCell As Range


With Worksheets("Travel Expense Voucher")
For Each myCell In .Range("U15:U45")
If myCell.Value > 0 And .Cells(myCell.row, "N") = "" Then

MsgBox "Project Number must be provided on each line where reimbursement is being claimed.", vbInformation, "Important:"
alreadyPrompted = True

End If
Next myCell
End With
End Sub

 

[KristenB]

I just wanted to update on this thread post, I was able to get the 'critical' icon on my msgbox.. but still cannot get it to only present 1 time.

 

[Logit]

.
Try this :

Sub ProjNumbrReq()
                                                
Static alreadyPrompted As Boolean
                                                
Dim myCell As Range
                                                
                                                
With Worksheets("Travel Expense Voucher")
    For Each myCell In .Range("U15:U45")
        If myCell.Value > 0 And .Cells(myCell.Row, "N") = "" Then
            MsgBox "Project Number must be provided on each line where reimbursement is being claimed.", vbCritical, "Important:"
            Exit Sub
        End If
    Next myCell
End With
                                                
End Sub

 

[KristenB]

It was really that easy? I have spent the last 4 hours trying to figure out where I goofed. Thank you! Can you tell me how I will get this to work in my document? I am currently running it in the VBA screen and it works but I want it to run whenever the user selects the 'save or save as' buttons.

 

[Logit]

.

I want it to run whenever the user selects the 'save or save as' buttons.

Please be more specific. How is the User accessing the 'save or save as' buttons ?

 

[KristenB]

So I copied this coding into the 'Workbook' module and it worked fine the first time I selected 'run' but now it will not run again. Should it no run each time the workbook is saved as my other macro in that module does?

 

[KristenB]

The user is completing the document, they will then save the document as an excel file to send in for submission. I have moved the coding into the 'workbook' module with another macro that runs 'before save' but it will not work?

 

[Logit]

.
The macro should be pasted into a Regular Module not in the ThisWorkbook module.

To have the macro run when the user saves the workbook paste this macro into the ThisWorkbook module :

Option Explicit


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

 

[KristenB]

I'm getting the following error:

 

[Logit]

The error didn't display correctly.

???