message box

drubin25

New Member
Joined
Mar 19, 2016
Messages
38
I am trying to create a message box if a certain cell is equal to a certain rule and am having some difficulty.

If cell D26 = "create a special batch", I would like a box to appear that tells the user to "enter the special batch number", then click OK. Once they click OK, I would like that to go to a RECORDS tab in column Q. My current code is below:



VBA Code:
Sub SENDTOLOG_Click()

Application.ScreenUpdating = False

Dim copysheet As Worksheet
Dim pastesheet As Worksheet

Set copysheet = Worksheets("Input")
Set pastesheet = Worksheets("Records")

If Range("G4") = "" Then
      MsgBox "Please enter the CUSTOMER NAME" & vbCrLf & " " & vbCrLf & "XXXXXXX"
      Range("G4").Select
    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"
     
Else

MsgBox "THE RECORD HAS BEEN SAVED." & vbCrLf & " " & vbCrLf & "XXXXXXX."

'Copy data from the INPUT to the RECORDS worksheet

'copy CUSTOMER #
copysheet.Range("M4").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

'copy CUSTOMER NAME
copysheet.Range("G4").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 1).PasteSpecial xlPasteValues

'copy ENTRY DATE/TIME
copysheet.Range("D17").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 2).PasteSpecial xlPasteValues

'copy TICKET LOAD DATE
copysheet.Range("G14").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 3).PasteSpecial xlPasteValues

'copy LINE QTY
copysheet.Range("G7").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 4).PasteSpecial xlPasteValues

'copy QTY REJECTED
copysheet.Range("M7").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 5).PasteSpecial xlPasteValues

'copy % REJECTED
copysheet.Range("P7").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 6).PasteSpecial xlPasteValues

'copy RULE
copysheet.Range("G11").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 7).PasteSpecial xlPasteValues

'copy RESPONSE
copysheet.Range("D26").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 8).PasteSpecial xlPasteValues

'copy rejected by:
copysheet.Range("M14").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 9).PasteSpecial xlPasteValues

'copy before 2pm?
copysheet.Range("S24").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 10).PasteSpecial xlPasteValues

'copy load date after today?
copysheet.Range("T24").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 11).PasteSpecial xlPasteValues

'PO start w/ number?
copysheet.Range("U24").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 12).PasteSpecial xlPasteValues

'copy less than 10 rejected?
copysheet.Range("V24").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 13).PasteSpecial xlPasteValues

'copy 5 or less rejected?
copysheet.Range("X24").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 14).PasteSpecial xlPasteValues

'copy less than % rule?
copysheet.Range("Y24").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 15).PasteSpecial xlPasteValues


Sheets("Input").Activate

Application.CutCopyMode = False
Application.ScreenUpdating = True

End If
   

End Sub
 
Last edited by a moderator:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,355
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
if your Special Batch Nubmer is Number without Letters Try this:
VBA Code:
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"
Else
MsgBox "THE RECORD HAS BEEN SAVED." & vbCrLf & " " & vbCrLf & "XXXXXXX."

'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? , load date after today? , PO start w/ number? , less than 10 rejected?
Range(PS.Cells(Lr, 11), PS.Cells(Lr, 14)).Value = Range(CS.Cells(24, 19), CS.Cells(24, 22)).Value

'copy 5 or less rejected? , less than % rule?
Range(PS.Cells(Lr, 15), PS.Cells(Lr, 16)).Value = Range(CS.Cells(24, 24), CS.Cells(24, 25)).Value
End If

If Range("D26") = "create a special batch" Then
M = Application.InputBox(prompt:="Enter the special batch number", Type:=1)
PS.Cells(Lr, 17).Value = M
End If
Sheets("Input").Activate
Application.ScreenUpdating = True

End Sub
 

drubin25

New Member
Joined
Mar 19, 2016
Messages
38
I think this is trying to do what I am looking for, but I get the error below when it should be opening the box to enter the special batch number. What does this mean? Thank you again for your help.

1630866567280.png
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,355
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
It has very causes. search on Internet and try solutions give to you and test which one fix your problem.
Maybe you have protected sheet.
or you should change row to find lastrow, Then change at this line number 1 to others and try again:
Rich (BB code):
Lr = PS.Cells(Rows.Count, 1).End(xlUp).Row + 1
 
Last edited:

drubin25

New Member
Joined
Mar 19, 2016
Messages
38
I got that issue fixed. a box pops up if a special batch is needed, then another pops up to enter the special batch number. how do i take what is entered into the pop up box by the user and paste it in column Q for the row with the latest data? Code below-

lseIf 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:")
If sText = "" Or sText = False Then Exit Sub
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,355
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You don't need to Use MsgBox. Enter Message Box Text at Title for InputBox.
Type:=1 is for Numbers only. if you want Input Text it should be Type:=2 and if you want input text and Number then Use Type:=3
This is Guideline: Application.InputBox method (Excel)
Format for InputBox different than MsgBox.

Rich (BB code):
sText = Application.InputBox(prompt:="You Must Create Batch Number" & VbCrLF & "Enter the special batch number:", Type:=1)
and then If Condition based type:
 

Forum statistics

Threads
1,141,289
Messages
5,705,524
Members
421,399
Latest member
hjweiss00

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