I wrote the following code. I only want the Input box to pop up if the data in Action Combo Box is either "Cancelled" or "Refused" And, once the comment has been entered, to finish up the If/Then script, and jump down to wrap, without printing a report.
Every time the script runs, regardless of the data in Action Combo Box, the input box appears, and the script doesn't jump to the end, and a report is printed.
Have a nice day.
Dan
Every time the script runs, regardless of the data in Action Combo Box, the input box appears, and the script doesn't jump to the end, and a report is printed.
VBA Code:
Private Sub PostPrintCmdButton_Click()
Application.ScreenUpdating = False
Dim wb As Workbook
Dim ws As Worksheet
Dim Last_Row As Long
Dim tbl As ListObject
Dim sFileName As String
Dim sFileName1 As String
Dim ShID1 As Range
Dim ShID As Range
Dim Cmnt As Variant
Dim fpath As String
Set wb = ThisWorkbook
Set ws = wb.Sheets("FY")
Set tbl = wb.Sheets("FY").ListObjects("Data")
Set ShID = Worksheets("RPT").Range("D3")
Set ShID1 = Worksheets("RPT1").Range("C4")
sFileName = ShID.Value
sFileName1 = ShID1.Value
Cmnt = InputBox("Please briefly describe why the truck was canceled or refused.", "Update Comment")
fpath = wb.path & "\Intake"
Last_Row = Cells(Rows.Count, 1).End(xlUp).Row
'Check for refused or caneled shipments
If UserForm1.ActionCmbBox.Value = "Refused" Or UserForm1.ActionCmbBox.Value = "Cancelled" Then
ws.ListObjects("Data").Range(Last_Row, 28).Value = Cmnt
ws.ListObjects("Data").Range(Last_Row, 7).Value = UserForm1.ActionCmbBox.Value
ws.ListObjects("Data").Range(Last_Row, 1).Value = UserForm1.ShipmentIDTextBox.Value
ws.ListObjects("Data").Range(Last_Row, 6).Value = UserForm1.DateTimeTextBox.Value
GoTo Wrap:
End If
Continue:
If UserForm1.UPSRadioButton.Value = True Then
wb.Sheets("RPT1").Range("D9").Value = "X"
GoTo PrntRPT1:
ElseIf UserForm1.ULineRadioButton.Value = True Then
wb.Sheets("RPT1").Range("F9").Value = "X"
GoTo PrntRPT1:
ElseIf UserForm1.FedExRadioButton.Value = True Then
wb.Sheets("RPT1").Range("H9").Value = "X"
GoTo PrntRPT1:
ElseIf UserForm1.USPSRadioButton.Value = True Then
wb.Sheets("RPT1").Range("J9").Value = "X"
GoTo PrntRPT1:
ElseIf UserForm1.OtherCourierRadioButton.Value = True Then
wb.Sheets("RPT1").Range("L9").Value = "X"
GoTo PrntRPT1:
Else
GoTo PrntRPT:
PrntRPT1:
ws.ListObjects("Data").Range(Last_Row, 26).Value = "Misc non-warehouse goods"
ws.ListObjects("Data").Range(Last_Row, 1).Value = UserForm1.ShipmentIDTextBox.Value
ws.ListObjects("Data").Range(Last_Row, 6).Value = UserForm1.DateTimeTextBox.Value
ws.ListObjects("Data").Range(Last_Row, 7).Value = "Receiving"
ws.ListObjects("Data").Range(Last_Row, 28).Value = UserForm1.CommentTextBox.Value
ws.ListObjects("Data").Range(Last_Row, 24).Value = UserForm1.DoorCmbBox.Value
ws.ListObjects("Data").Range(Last_Row, 31).Value = UserForm1.DriverCellTextBox.Value
ws.ListObjects("Data").Range(Last_Row, 30).Value = UserForm1.DriverTextBox.Value
ws.ListObjects("Data").Range(Last_Row, 22).Value = UserForm1.PlateNumberTextBox.Value
ws.ListObjects("Data").Range(Last_Row, 25).Value = UserForm1.SealTextBox.Value
ws.ListObjects("Data").Range(Last_Row, 23).Value = UserForm1.StateComboBox.Value
ws.ListObjects("Data").Range(Last_Row, 18).Value = UserForm1.TrailerNumberTextBox.Value
ws.ListObjects("Data").Range(Last_Row, 17).Value = UserForm1.TruckNumberTextBox.Value
Set ws = wb.Worksheets("RPT1")
Sheets("RPT1").Activate
ws.Range("C4").Value = UserForm1.ShipmentIDTextBox.Value
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
fpath & "\" & sFileName1 & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
ActiveSheet.Range("D9").ClearContents
ActiveSheet.Range("F9").ClearContents
ActiveSheet.Range("H9").ClearContents
ActiveSheet.Range("J9").ClearContents
ActiveSheet.Range("L9").ClearContents
ActiveSheet.Range("C4").ClearContents
Set ws = wb.Worksheets("FY")
GoTo Wrap:
End If
PrntRPT:
'Transfer UserForm data to table
ws.ListObjects("Data").Range(Last_Row, 1).Value = UserForm1.ShipmentIDTextBox.Value
ws.ListObjects("Data").Range(Last_Row, 6).Value = UserForm1.DateTimeTextBox.Value
ws.ListObjects("Data").Range(Last_Row, 7).Value = UserForm1.ActionCmbBox.Value
ws.ListObjects("Data").Range(Last_Row, 12).Value = UserForm1.ASNTextBox.Value
ws.ListObjects("Data").Range(Last_Row, 16).Value = UserForm1.CarrierTextBox.Value
ws.ListObjects("Data").Range(Last_Row, 28).Value = UserForm1.CommentTextBox.Value
ws.ListObjects("Data").Range(Last_Row, 10).Value = UserForm1.CSTextBox.Value
ws.ListObjects("Data").Range(Last_Row, 24).Value = UserForm1.DoorCmbBox.Value
ws.ListObjects("Data").Range(Last_Row, 9).Value = UserForm1.DOTextBox.Value
ws.ListObjects("Data").Range(Last_Row, 31).Value = UserForm1.DriverCellTextBox.Value
ws.ListObjects("Data").Range(Last_Row, 30).Value = UserForm1.DriverTextBox.Value
ws.ListObjects("Data").Range(Last_Row, 22).Value = UserForm1.PlateNumberTextBox.Value
ws.ListObjects("Data").Range(Last_Row, 25).Value = UserForm1.SealTextBox.Value
ws.ListObjects("Data").Range(Last_Row, 23).Value = UserForm1.StateComboBox.Value
ws.ListObjects("Data").Range(Last_Row, 18).Value = UserForm1.TrailerNumberTextBox.Value
ws.ListObjects("Data").Range(Last_Row, 17).Value = UserForm1.TruckNumberTextBox.Value
ws.ListObjects("Data").Range(Last_Row, 13).Value = UserForm1.WSATextBox.Value
'Transfer Input to shipping and receiving form
Set ws = wb.Sheets("RPT")
ws.Activate
ws.Range("D3").Value = UserForm1.ShipmentIDTextBox.Value
ws.Calculate
'Export form to pdf and open it for printing
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
fpath & "\" & sFileName & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
Wrap:
Unload UserForm1
Application.ScreenUpdating = True
Sheets("Start").Activate
End Sub
Have a nice day.
Dan