Results 1 to 6 of 6

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

    Code:
        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

    Last edited by Bob Phillips; 2013-03-09 at 11:46 PM. Reason: Tidied code up as best I could

  2. #2
    Maybe

    Code:
        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
                    End If
                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.
                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

  3. #3
    Your solution still bypasses date field if empty and moves to check AX17.

    Macro must stop running if W17 is empty.

  4. #4
    I thought that was what you wanted, after all if it is empty why would you test for a valid date?

  5. #5
    Because quotation or invoice cannot go out without a date.

    When I execute the macro it does the following;

    1. Checks that a customer has been selected, if not macro stops;
    2. Checks for invoice date which can't be empty, if is macro stops there;
    3. Checks for who processed the document, if not macro stops there;
    4. Checks for a value for quote or invoice which must be higher then 0, if not then stop;
    5. if all the above are meat, then macro saves this document in pdf format
    6. prints 3 copies, original, duplicate, triplicate,
    7. extracts certain data to another spreadsheet and saves it,
    8. clears all noon protected fields in invoice,
    9. increases document number by 1.


    I am amazed to what i have managed so far with great big help from you guys to which I am very very great-full.

    Would not ever make it on my own.

    My brother is going to own me big time. lol.

    Thank you mate.

    Regards,
    Albert

  6. #6
    How about this

    Code:
        Else        If Range("W17").Value = vbNullString 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").Value = vbNullString 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.
                Range("AZ73").Select
                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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •