Macro stops running if date is not in past...

[Carlos Arruda]

Sorry everybody but when I thought I was getting to grasps with this it's all going down the hill.

The code bellow checks for;

W17 which is a delivery date field. This is working correct. It displays message if date is in the past.

Now the problem is, if date is in the past I get message and if ok then the macro runs to the next step and checks for cell AX17 processed by.

Now if date is not in the past the macro does not carries on to check AX17, it just stops...

Where in the code bellow am I going wrong please?

Thank you.

Albert

Else    If Range("W17") = Empty Then 'Checks if there is a delivery date.
        MsgBox "Please add a delivery date!", vbInformation, "Delivery date..."
        Range("W17").Select
                
        Else
        If Range("w17").Value < Date Then
        answer = MsgBox("The delivery date is set in the past." & vbNewLine & "Click OK if date is correct." & vbNewLine & "Click Cancel to change.", vbQuestion + vbOKCancel, "Delivery date!")
        If answer = vbCancel Then 'Exit Sub*****************------------------
        Range("w17").Select
    Else
    If Range("AX17") = Empty Then 'Checks if there is who processed invoice.
        MsgBox "Please select Processed By!", vbInformation, "Processed by..."
        Range("AX17").Select
    Else
    If Range("AZ73").Value = 0 Then
        MsgBox "Invoice cannot be £0.00!", vbInformation, "Invoice total..." 'Checks invoice total must be greater than 0.
    Else
    Application.ActivePrinter = "doPDF v7 on DOP7:" 'Selects doPDF to genarate PDF file of invoice.
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "E:\Lusa\Generated Invoices\INV" & Range("L17").Text & ".pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False
    If Application.Dialogs(xlDialogPrinterSetup).Show Then
    End If

[rollis13]

Just break it down al litle more to:

Sub test()
    If Range("W17") = Empty Then 'Checks if there is a delivery date.
        MsgBox "Please add a delivery date!", vbInformation, "Delivery date..."
        Range("W17").Select
        Exit Sub
    End If
    If Range("W17").Value < Date Then
        answer = MsgBox("The delivery date is set in the past." & vbNewLine & "Click OK if date is correct." & vbNewLine & "Click Cancel to change.", vbQuestion + vbOKCancel, "Delivery date!")
        If answer = vbCancel Then
            Range("W17").Select
            Exit Sub
        End If
    End If
    If Range("AX17") = Empty Then 'Checks if there is who processed invoice.
        MsgBox "Please select Processed By!", vbInformation, "Processed by..."
        Range("AX17").Select
    Else
        If Range("AZ73").Value = 0 Then
            MsgBox "Invoice cannot be £0.00!", vbInformation, "Invoice total..." 'Checks invoice total must be greater than 0.
            Exit Sub
        End If
        Application.ActivePrinter = "doPDF v7 on DOP7:" 'Selects doPDF to genarate PDF file of invoice.
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "E:\Lusa\Generated Invoices\INV" & Range("L17").Text & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
        If Application.Dialogs(xlDialogPrinterSetup).Show Then
        End If
    End If
End Sub

[Carlos Arruda]

Hi mate.

I am sorry but I should have posted the entire macro as the code you have suggested once in place it started producing if errors with end if statements...

Bellow is the entire macro. The problem is marked in red.

If the date is set in the past the macro picks it up and asks to click ok if correct. If I click OK it's fine it moves to the next stage and checks for the AX17 and so on.

The problem is only when the date is not in the past that the macro stays stuck and does not go pass the date and returns no error either. It should move to check on field AX17 but it doesn't.

Thank you.

Albert

Sub Check_Info()
    Dim i As Long, D, E
       D = Array("Original", "Duplicate", "Triplicate")
       E = Array("- Customer Copy -", "- Customer Paid Copy -", "- Accounts Copy -")
    
    If Range("AS1") = Empty Then 'Checks if customer has been selected.
        MsgBox "No Customer selected!", vbInformation, "Customer..."
        Range("AS1").Select
    Else
    If Range("W17") = Empty Then 'Checks if there is a delivery date.
        MsgBox "Please add a delivery date!", vbInformation, "Delivery date..."
        Range("W17").Select
                
        Else
        If Range("w17").Value < Date Then
        answer = MsgBox("The delivery date is set in the past." & vbNewLine & "Click OK if date is correct." & vbNewLine & "Click Cancel to change.", vbQuestion + vbOKCancel, "Delivery date!")
        If answer = vbCancel Then 'Exit Sub*****************------------------
        Range("w17").Select
    Else
    If Range("AX17") = Empty Then 'Checks if there is who processed invoice.
        MsgBox "Please select Processed By!", vbInformation, "Processed by..."
        Range("AX17").Select
    Else
    If Range("AZ73").Value = 0 Then
        MsgBox "Invoice cannot be £0.00!", vbInformation, "Invoice total..." 'Checks invoice total must be greater than 0.
    Else
    Application.ActivePrinter = "doPDF v7 on DOP7:" 'Selects doPDF to genarate PDF file of invoice.
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "E:\Lusa\Generated Invoices\INV" & Range("L17").Text & ".pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False
    If Application.Dialogs(xlDialogPrinterSetup).Show Then
    End If
                        
    Sheets("Invoice").Unprotect Password:="*****" 'Unprotects Saved Invoices sheet to record data from Invoice sheet.
                
        With ActiveSheet
            For i = 0 To 2
                .Range("T10").Value = D(i)
                .Range("T12").Value = E(i)
                .PrintOut Copies:=1, Collate:=True
            Next i
        End With
           
    Sheets("Saved Invoices").Unprotect Password:="*****" 'Unprotects Saved Invoices sheet so that it can record data pulled from Invoice sheet.
   
    Dim Data(1 To 4) As Variant
    Dim DstRng As Range
    Dim RngEnd As Range
   
        Set DstRng = Worksheets("Saved Invoices").Range("A2:D2")
        Set RngEnd = DstRng.Parent.Cells(Rows.Count, DstRng.Column).End(xlUp)
        Set DstRng = IIf(RngEnd.Row < DstRng.Row, DstRng, RngEnd.Offset(1, 0).Resize(1, 4))
       
        With Worksheets("Invoice")
            Data(1) = .Range("L17")  'Invoice number
            Data(2) = .Range("A17")  'Date
            Data(3) = .Range("AS1")  'Customer
            Data(4) = .Range("AZ73") 'Amount
        End With
       
        DstRng = Data
                   
        Sheets("Saved Invoices").Protect Password:="*****" 'Protects Saved Invoices sheet so that data is not erased.
        
        Range( _
        "AS1:BH1,W17:AJ17,AX17:BH17,I20:AD67,AJ20:AL67,AV20:BB67,G70:T70,AA70:AL70,G71:AL71,G74:P74,G75:P75,Z74:AL74,Z75:AL75,T10,T12" _
        ).Select
        Range("Z75").Activate
        Selection.ClearContents
        
        Range("A1:BY1").Select 'Selects cells at top to which zoom is based.
        ActiveWindow.Zoom = True
        Range("AS1").Select
        ActiveWindow.LargeScroll Down:=-5
 
        With Range("L17")
        .NumberFormat = "00000"
        .Value = .Value + 1
        End With
 
        Sheets("Invoice").Protect Password:="*****" 'Protects the Invoice sheet.
   
        ActiveWorkbook.Save
 
    End If
    End If
    End If
    End If
    End If
    End If
       
End Sub

[Carlos Arruda]

'Else
        'If Range("w17").Value < Date Then
        'answer = MsgBox("The delivery date is set in the past." & vbNewLine & "Click OK if date is correct." & vbNewLine & "Click Cancel to change.", vbQuestion + vbOKCancel, "Delivery date!")
        'If answer = vbCancel Then 'Exit Sub*****************------------------
        'Range("w17").Select

As you may see I have ' marked above steeps as description so that macro would ignore this one steep and macro continues to run as it should do.

The problem is in the lines above.

If date is in the past macro returns alert and cancels it if I cancel or continues to next steep.

But if the date is not in the past then macro stops at this steep.

I can't figure it out what is wrong.

Any help truly appreciated.

Regards,
Albert

[rollis13]

As said, break it down.
This could be your logic:

...
test AS1 = If Range("AS1") = Empty Then
msg      = MsgBox "No Customer selected!", vbInformation, "Customer..."
select   = Range("AS1").Select
exit     = Exit Sub
close If = End If

test W17 = If Range("W17") = Empty Then
msg      = MsgBox "Please add a delivery date!", vbInformation, "Delivery date..."
select   = Range("W17").Select
exit     = Exit Sub
close If = End If
...

So:

Sub Check_Info()
    Dim i As Long, D, E
    Dim answer As Long
    D = Array("Original", "Duplicate", "Triplicate")
    E = Array("- Customer Copy -", "- Customer Paid Copy -", "- Accounts Copy -")
    
    If Range("AS1") = Empty Then 'Checks if customer has been selected.
        MsgBox "No Customer selected!", vbInformation, "Customer..."
        Range("AS1").Select
        Exit Sub
    End If
    If Range("W17") = Empty Then 'Checks if there is a delivery date.
        MsgBox "Please add a delivery date!", vbInformation, "Delivery date..."
        Range("W17").Select
        Exit Sub
    End If
    If Range("W17").Value < Date Then
        answer = MsgBox("The delivery date is set in the past." & vbNewLine & "Click OK if date is correct." & vbNewLine & "Click Cancel to change.", vbQuestion + vbOKCancel, "Delivery date!")
        If answer = vbCancel Then Range("W17").Select: Exit Sub
    End If
    If Range("AX17") = Empty Then 'Checks if there is who processed invoice.
        MsgBox "Please select Processed By!", vbInformation, "Processed by..."
        Range("AX17").Select
        Exit Sub
    End If
    If Range("AZ73").Value = 0 Then
        MsgBox "Invoice cannot be £0.00!", vbInformation, "Invoice total..." 'Checks invoice total must be greater than 0.
        Exit Sub
    End If
    
' =========== and so on ================================
    Application.ActivePrinter = "doPDF v7 on DOP7:" 'Selects doPDF to genarate PDF file of invoice.
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "E:\Lusa\Generated Invoices\INV" & Range("L17").Text & ".pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False
    
    Application.Dialogs(xlDialogPrinterSetup).Show  '<== changed
                        
    Sheets("Invoice").Unprotect Password:="*****" 'Unprotects Saved Invoices sheet to record data from Invoice sheet.
                
    With ActiveSheet
        For i = 0 To 2
            .Range("T10").Value = D(i)
            .Range("T12").Value = E(i)
            .PrintOut Copies:=1, Collate:=True
        Next i
    End With
           
    Sheets("Saved Invoices").Unprotect Password:="*****" 'Unprotects Saved Invoices sheet so that it can record data pulled from Invoice sheet.
   
    Dim Data(1 To 4) As Variant
    Dim DstRng As Range
    Dim RngEnd As Range
   
    Set DstRng = Worksheets("Saved Invoices").Range("A2:D2")
    Set RngEnd = DstRng.Parent.Cells(Rows.Count, DstRng.Column).End(xlUp)
    Set DstRng = IIf(RngEnd.Row < DstRng.Row, DstRng, RngEnd.Offset(1, 0).Resize(1, 4))
   
    With Worksheets("Invoice")
        Data(1) = .Range("L17")  'Invoice number
        Data(2) = .Range("A17")  'Date
        Data(3) = .Range("AS1")  'Customer
        Data(4) = .Range("AZ73") 'Amount
    End With
   
    DstRng = Data
    Sheets("Saved Invoices").Protect Password:="*****" 'Protects Saved Invoices sheet so that data is not erased.
    Range( _
    "AS1:BH1,W17:AJ17,AX17:BH17,I20:AD67,AJ20:AL67,AV20:BB67,G70:T70,AA70:AL70,G71:AL71,G74:P74,G75:P75,Z74:AL74,Z75:AL75,T10,T12" _
    ).Select
    Range("Z75").Activate
    Selection.ClearContents
    Range("A1:BY1").Select 'Selects cells at top to which zoom is based.
    ActiveWindow.Zoom = True
    Range("AS1").Select
    ActiveWindow.LargeScroll Down:=-5
    
    With Range("L17")
        .NumberFormat = "00000"
        .Value = .Value + 1
    End With
    
    Sheets("Invoice").Protect Password:="*****" 'Protects the Invoice sheet.
    ActiveWorkbook.Save
       
End Sub