| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Macro stops running if date is not in past...
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
|
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro stops running if date is not in past...
Alberto,
Without seeing your entire code, it is hard to say exactly where the issue is. However, I would bet that there is a missing "End If" to blame. I added some indentations to your code to see how the If/Else/End If lines match up and I added a few "End If" lines where they seemed to be missing. If this doesn't work, you may need to post your entire code. Ben 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 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. 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 End If |
|
#3
|
|||
|
|||
|
Hi.
I understand what you mean. I have all the end ifs at the bottom and this wasn't the problem as it worked before. The only diference was that I am now checking for the date to see if it's in the past so I have added this statement to it; Code:
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
Exit Sub
Thank you and hope you can have a look at the whole code given here. Regards, Albert Code:
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
Exit Sub
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
|
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro stops running if date is not in past...
Alberto,
It is not just that each IF must end with an END IF, it is that the END IF for the date check should be in the middle, not the end. Try this adjustment. After the lines: Range("w17").Select Exit Sub Else replace the "ELSE" line with two "END IF" lines and remove two of the End Ifs from the bottom. For example, your full code (minus the part where your macro actually performs actions) would read: 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 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. Else 'perform all of the actions End If End If End If End If End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Code Stops Running Instead of Debugging | Excel Discussion (Misc queries) | |||
| Macro stops running at an unexpected time | Excel Programming | |||
| Excel VBA Macro stops running when another program is activated | Excel Programming | |||
| why excel stops running?? | Excel Discussion (Misc queries) | |||
| Macro stops running after file has moved | Excel Programming | |||
Linear Mode
