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]

Well that didn't work... this is my coding in ThisWorkbook and I am getting an AmbiguousName error on Workbook_BeforeSave

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


'When a row begins with X in Travel Expense Codes worksheet, hide the row


Const beginRow As Long = 3
Const endRow As Long = 38
Const chkCol As Long = 14


Dim rowCnt As Long
Dim ws As Worksheet


Set ws = ThisWorkbook.Worksheets("Travel Expense Codes")


For rowCnt = endRow To beginRow Step -1
With ws.Cells(rowCnt, chkCol)
.EntireRow.Hidden = (.Value = "X")
End With

Next rowCnt


End Sub




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

 

[Logit]

.
You can only use Before Save once in ThisWorkbook module :

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




'When a row begins with X in Travel Expense Codes worksheet, hide the row




Const beginRow As Long = 3
Const endRow As Long = 38
Const chkCol As Long = 14




Dim rowCnt As Long
Dim ws As Worksheet




Set ws = ThisWorkbook.Worksheets("Travel Expense Codes")




    For rowCnt = endRow To beginRow Step -1
        With ws.Cells(rowCnt, chkCol)
            .EntireRow.Hidden = (.Value = "X")
        End With
    
    Next rowCnt


[B][COLOR=#ff0000]ProjNumbrReq[/COLOR][/B]


End Sub

 

[KristenB]

I had a feeling that was the case but I couldn't figure out how else to put that in there. However, now I'm getting an error that 'sub or function not defined'

 

[Logit]

.
Ok .... in a Regular Module paste this macro :

Option Explicit


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

In ThisWorkbook module paste this macro :

Option Explicit


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ProjNumbrReq
End Sub

Make certain both of these macros are not located in any other locations in the workbook modules.

In order for the macro ProjNumbrReq to run automatically, it must be located in the Workbook_BeforeClose event. Otherwise you will continue to receive the error
as before.

 

[Logit]

.
It is very late here. Need to sleep.

I'll check this in the morning to see how it turned out.

 

[Logit]

?????