If Statement with Input Box Issue

dslaugh

New Member
Joined
Jul 16, 2020
Messages
4
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.


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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,961
Then move the InputBox line inside the
VBA Code:
If UserForm1.ActionCmbBox.Value = "Refused" Or UserForm1.ActionCmbBox.Value = "Cancelled" Then
block
 

Watch MrExcel Video

Forum statistics

Threads
1,127,532
Messages
5,625,371
Members
416,098
Latest member
jpmiralrio

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top