If then VBA

drubin25

New Member
Joined
Mar 19, 2016
Messages
38
I am having difficulty creating the code that if a certain condition is not met, it cycles to the next. The code is orange works great, but I need it to do the following afterwards if the next condition is not met:

IF D26 = "DO NOT CREATE A SPECIAL BATCH AND DO NOT BACKORDER.", then a message box pops up saying, ""DO NOT CREATE A SPECIAL BATCH AND DO NOT BACKORDER. NOTHING IS REQUIRED OF YOU FOR THIS ITEM."

If that condition is not met and D26 = "SEND TO OFFICE TO CREATE A BACKORDER.", then a message box pops up saying, "THE RULES FOR THIS CUSTOMER REQUIRE THE FRONT OFFICE TO CREATE A BACKORDER. DO NOT ENTER A SPECIAL BATCH. THE FRONT OFFICE HAS BEEN NOTIFIED, AND NOTHING FURTHER IS REQUIRED." Also, if this condition is met, I would like it to send an email to someone saying, "A BACKORDER IS REQUIRED FOR [input worskeet cell G4], QTY [input worskeet cell M7], PO NUMBER [input worskeet cell G20].

Once all of these are done, I would like the MsgBox "THE RECORD HAS BEEN SAVED." & vbCrLf & " " & vbCrLf & "XXXXXXX." to pop up.

I currently have the following:

Sub SENDTOLOG_Click()
Application.ScreenUpdating = False
Dim CS As Worksheet, PS As Worksheet, lr As Long, M As Long

Set CS = Worksheets("Input")
Set PS = Worksheets("Records")

If Range("G4") = "" Then
MsgBox "Please enter the CUSTOMER NAME" & vbCrLf & " " & vbCrLf & "XXXXXXX"
ElseIf Range("G7") = "" Then
MsgBox "Please enter the LINE QUANTITY" & vbCrLf & " " & vbCrLf & "XXXXXXX"
ElseIf Range("M7") = "" Then
MsgBox "Please enter the QTY REJECTED" & vbCrLf & " " & vbCrLf & "XXXXXXX"
ElseIf Range("G14") = "" Then
MsgBox "Please enter the TICKET LOAD DATE" & vbCrLf & " " & vbCrLf & "XXXXXXX"
ElseIf Range("M14") = "" Then
MsgBox "Please enter your name in the REJECTED BY: BOX" & vbCrLf & " " & vbCrLf & "XXXXXXX"
ElseIf Range("G20") = "" Then
MsgBox "Please enter the PO NUMBER" & vbCrLf & " " & vbCrLf & "XXXXXXX"
ElseIf Worksheets("Input").Range("D26").Value = "CREATE SPECIAL BATCH" Then
MsgBox ("YOU MUST CREATE A SPECIAL BATCH")
sText = Application.InputBox("ENTER THE SPECIAL BATCH TICKET NUMBER THAT WAS CREATED: " & vbCrLf & "XXXXXXX:")

MsgBox "THE RECORD HAS BEEN SAVED." & vbCrLf & " " & vbCrLf & "XXXXXXX."
'Sheets("Input").Activate
'Application.ScreenUpdating = True

'Find First Empty Row after Data
lr = PS.Cells(Rows.Count, 1).End(xlUp).Row + 1
'Copy data from the INPUT to the RECORDS worksheet

'copy CUSTOMER #
PS.Cells(lr, 1).Value = CS.Range("M4").Value
'copy CUSTOMER NAME
PS.Cells(lr, 2).Value = CS.Range("G4").Value
'copy ENTRY DATE/TIME
PS.Cells(lr, 3).Value = CS.Range("D17").Value
'copy TICKET LOAD DATE
PS.Cells(lr, 4).Value = CS.Range("G14").Value
'copy LINE QTY
PS.Cells(lr, 5).Value = CS.Range("G7").Value
'copy QTY REJECTED
PS.Cells(lr, 6).Value = CS.Range("M7").Value
'copy % REJECTED
PS.Cells(lr, 7).Value = CS.Range("P7").Value
'copy RULE
PS.Cells(lr, 8).Value = CS.Range("G11").Value
'copy RESPONSE
PS.Cells(lr, 9).Value = CS.Range("D26").Value
'copy rejected by:
PS.Cells(lr, 10).Value = CS.Range("M14").Value
'copy before 2pm:
PS.Cells(lr, 11).Value = CS.Range("S24").Value
'copy load date after today?:
PS.Cells(lr, 12).Value = CS.Range("T24").Value
'copy PO start w/ number?:
PS.Cells(lr, 13).Value = CS.Range("U24").Value
'copy less than 10 rejected?:
PS.Cells(lr, 14).Value = CS.Range("V24").Value
'copy 5 or less rejected?:
PS.Cells(lr, 15).Value = CS.Range("X24").Value
'copy less than % rule?:
PS.Cells(lr, 16).Value = CS.Range("Y24").Value

End Sub
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,296
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Ok set G4 = "" and then test the following to see if it does what you want:

VBA Code:
Sub test()
'
    Application.ScreenUpdating = False
'
    Dim CS As Worksheet, PS As Worksheet, lr As Long        ' , M As Long
    Dim ArSourceAddress()   As Variant
    Dim CustName            As String
'
    Set CS = Worksheets("Input")
    Set PS = Worksheets("Records")
'
    If CS.Range("G4") = "" Then
        Do
            CustName = Application.InputBox(Prompt:="Please enter the CUSTOMER NAME" & vbCrLf & " " & vbCrLf & "XXXXXXX", Type:=2)  ' Text entry
        Loop Until CustName <> vbNullString And CustName <> False
'
        CS.Range("G4").Value = CustName
    End If
End Sub

That code should prevent the user from cancelling, and clicking the X button at the top right of the box.

Is that what you want? To keep asking for the info until the info is entered?

The code only checks G4. I wanted to make sure that works the exact way you want, prior to me doing anything else.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

drubin25

New Member
Joined
Mar 19, 2016
Messages
38
I got the end with fixed, but when one of those cells is bank (including G4) it continues to run. it does not tell the operator that data needs to be entered,
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,296
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
@drubin25 One step at a time. The last code I provided is a separate module to test just the G4 cell. It does not get put into your other code, it is a separate module. There is no missing end with because it doesn't have a with to begin with.

Test it as a separate module. It is designed to see if it handles the missing G4 entry only.

Let me know if that is how you want G4 handled.
 

drubin25

New Member
Joined
Mar 19, 2016
Messages
38

ADVERTISEMENT

ah got it. Yes, you are correct. This is how I would like it to work. Thank you!
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,296
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Is the PO NUMBER strictly numbers only or can there be letters or other stuff?
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,296
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

Same question for the 'SPECIAL BATCH TICKET NUMBER'.
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,296
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Ok I think this incorporates all of the code that you have provided thus far. Test it out and let us know if anything needs corrected.

VBA Code:
Sub test2()
'
''    Application.ScreenUpdating = False
'
    Dim CS As Worksheet, PS As Worksheet, lr As Long        ' , M As Long
    Dim LineQuantity        As Long
    Dim QtyRejected         As Long
    Dim TicketLoadDate      As String
    Dim ArSourceAddress()   As Variant
    Dim CustName            As Variant
    Dim POnumber            As Variant
    Dim RejectedBy          As Variant
    Dim SpecialBatchNumber  As Variant
'
    Set CS = Worksheets("Input")
    Set PS = Worksheets("Records")
'
    If CS.Range("G4") = "" Then
        Do
            CustName = Application.InputBox(Prompt:="Please enter the CUSTOMER NAME" & vbCrLf & " " & vbCrLf & "XXXXXXX")  ' Text entry
        Loop Until CustName <> vbNullString And CustName <> False
'
        CS.Range("G4").Value = CustName
    End If
'
    If CS.Range("G7") = "" Then
        Do
            LineQuantity = Application.InputBox(Prompt:="Please enter the LINE QUANTITY" & vbCrLf & " " & vbCrLf & "XXXXXXX", Type:=1)  ' Numeric Entry
        Loop Until LineQuantity <> 0 And LineQuantity <> False
'
        CS.Range("G7").Value = LineQuantity
    End If
'
    If CS.Range("M7") = "" Then
        Do
            QtyRejected = Application.InputBox(Prompt:="Please enter the QTY REJECTED" & vbCrLf & " " & vbCrLf & "XXXXXXX", Type:=1)    ' Numeric Entry
        Loop Until QtyRejected <> 0 And QtyRejected <> False
'
        CS.Range("M7").Value = QtyRejected
    End If
'
    If CS.Range("G14") = "" Then
        Do
            TicketLoadDate = Application.InputBox(Prompt:="Please enter the TICKET LOAD DATE" & vbCrLf & " " & vbCrLf & "XXXXXXX")      ' Date Entry
        Loop Until IsDate(TicketLoadDate) = True
'
        CS.Range("G14").Value = TicketLoadDate
    End If
'
    If CS.Range("M14") = "" Then
        Do
            RejectedBy = Application.InputBox(Prompt:="Please enter your name in the REJECTED BY: BOX" & vbCrLf & " " & vbCrLf & "XXXXXXX") ' Text entry
        Loop Until RejectedBy <> vbNullString And RejectedBy <> False
'
        CS.Range("M14").Value = RejectedBy
    End If
'
    If CS.Range("G20") = "" Then
        Do
            POnumber = Application.InputBox(Prompt:="Please enter the PO NUMBER" & vbCrLf & " " & vbCrLf & "XXXXXXX")  ' Text entry
        Loop Until POnumber <> vbNullString And POnumber <> False
'
        CS.Range("G20").Value = POnumber
    End If
'
    If CS.Range("D26").Value = "CREATE SPECIAL BATCH" Then
        MsgBox ("YOU MUST CREATE A SPECIAL BATCH")
'
        Do
            SpecialBatchNumber = Application.InputBox(Prompt:="ENTER THE SPECIAL BATCH TICKET NUMBER THAT WAS CREATED: " & vbCrLf & "XXXXXXX:") ' Text entry
        Loop Until SpecialBatchNumber <> vbNullString And SpecialBatchNumber <> False
'
        lr = PS.Range("A" & PS.Rows.Count).End(xlUp).Row + 1            ' Find First Empty Row after Data

        PS.Range("Q" & lr).Value = SpecialBatchNumber
    End If
'
    If CS.Range("D26").Value = "DO NOT CREATE A SPECIAL BATCH AND DO NOT BACKORDER." Then
        MsgBox ("DO NOT CREATE A SPECIAL BATCH AND DO NOT BACKORDER. NOTHING IS REQUIRED OF YOU FOR THIS ITEM.")
    End If
'
    If CS.Range("D26").Value = "SEND TO OFFICE TO CREATE A BACKORDER." Then
        lr = PS.Range("A" & PS.Rows.Count).End(xlUp).Row + 1            ' Find First Empty Row after Data
'
'       You didn't mention what to do here with the lr
'
        MsgBox "THE RECORD HAS BEEN SAVED." & vbCrLf & " " & vbCrLf & "XXXXXXX."
    End If
'
    lr = PS.Cells(Rows.Count, 1).End(xlUp).Row + 1                  ' Find First Empty Row after Data
'
'   Copy data from the INPUT to the RECORDS worksheet
    ArSourceAddress = Array("M4", "G4", "D17", "G14", "G7", "M7", "P7", "G11", "D26", "M14")
'
    For I = 0 To UBound(ArSourceAddress)
        PS.Cells(lr, I + 1).Value = CS.Range(ArSourceAddress(I)).Value           ' Columns 1 thru 10 .... Array addresses
    Next
'
    PS.Cells(lr, 11).Resize(, 4).Value = CS.Range("S24").Resize(, 4).Value  'Columns 11,12,13,14 .... S24,T24,U24,V24
'
    PS.Cells(lr, 15).Resize(, 2).Value = CS.Range("X24").Resize(, 2).Value  ' Columns 15,16 .... X24,Y24

''Application.ScreenUpdating = True
End Sub
 

Forum statistics

Threads
1,141,060
Messages
5,704,039
Members
421,323
Latest member
Exidous

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