Error in code with If, And, Else

[Kristen_cancerkiller]

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

'If there is a value greater than 0 in U for this row, and column n for this row
'is blank, then show an error message that a project number must be entered or no
'reimbursement will be given.

      If Worksheets("Travel Expense Voucher").Range("N15:N45")= "", And
      If Worksheets("Travel Expense Voucher").Range("U15:U45") > 0 Then
      MsgBox "Project Number must be provided for all lines where reimbursement is being requested" & vbCrLf & _
        Cancel = True
   End If
End Sub

 

[NARAYANK991]

Hi ,

Try this , and if it does not work , upload your complete workbook with the data and code in it.

Dim thisrow As Long

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

'       If there is a value greater than 0 in U for this row, and column n for this row
'       is blank, then show an error message that a project number must be entered or no
'       reimbursement will be given.

        thisrow = 37 ' this is a dummy statement ; the variable thisrow should get its value from some other procedure
'                      remove this statement when incorporating this code into your file

        If (Worksheets("Travel Expense Voucher").Cells(thisrow, 14) = "") And (Worksheets("Travel Expense Voucher").Cells(thisrow, 21) > 0) Then
           MsgBox "Project Number must be provided for all lines where reimbursement is being requested" & vbCrLf
           Cancel = True
        End If
End Sub

Narayan

 

[Kristen_cancerkiller]

Okay, so then I have the following code and it works well, however, I want this code to run whenever the user selects 'save' or 'save as'. Can you tell me how I make that happen?

Private 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

 

[NARAYANK991]

Hi ,

See if this works.

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

'       If there is a value greater than 0 in U for this row, and column n for this row
'       is blank, then show an error message that a project number must be entered or no
'       reimbursement will be given.

        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:"
                    Cancel = True
                    Exit Sub
                 End If
             Next myCell
        End With
End Sub

Narayan

 

[Kristen_cancerkiller]

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

 

[NARAYANK991]

Hi ,

Can you please upload your complete workbook with all of the code in it ?

This version of the Workbook_BeforeSave is different from the version you posted earlier.

Which is the correct version which you want should run ?

Narayan

 

[Kristen_cancerkiller]

It still isn't working. I stopped with row 38 on the initial coding because that code hides rows on the separate worksheet 'Travel Expense Codes'. The new coding is for the 'Travel Expense Voucher'. Multiple worksheets in this workbook project.

When I closed my developer screen and selected 'save', none of the commands were completed. I wonder what I'm doing wrong?

 

[NARAYANK991]

It still isn't working.
When I closed my developer screen and selected 'save', none of the commands were completed. I wonder what I'm doing wrong?

Hi ,

When you clicked on the Close button , what actually happened ? Did the workbook just close ?

Did you restore the procedure name to BeforeSave , or has it remained BeforeSave1 ?

Narayan

 

[Kristen_cancerkiller]

I had forgotten to change to BeforeSave... it is working now. Thank you so much!!!

 

[Hany ali]

please move thios Post to
VBA Macros
Dep.