Kindly help on VBA code

[HYKE]

Hi,

I have compiled this code (compiled because I consolidated the codes from various sources).This code is working fine except for one (in red highlight). What is wrong in the code and can somebody re-arrange the code for me. Thank you in advance for the help.

Private Sub exMobil_Click()
'On Error GoTo ErrHandler:
Dim lRow, CrOW As Long
Dim lPart As Long
Dim Answer As String
Dim MyNote As String
Dim Response As Integer
Dim ws As Worksheet
Dim cs As Worksheet
Set ws = Worksheets("EXXONMOBIL")
Set cs = Worksheets("CONSOLIDATED")
lRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row
CrOW = cs.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row
lPart = Me.moProdbox.ListIndex
  If Trim(Me.cType.Value) = "" Then
    MsgBox "Some Entry fields (If not all) is empty! Kindly fill up the required data before hitting the update button.", vbInformation, "Pre-Shipment Receipt Update"
    Exit Sub
  End If
MyNote = "If you are sure with the data,press OK else to double check press CANCEL"
    'Display MessageBox
Answer = MsgBox(MyNote, vbQuestion + vbOKCancel, "Pre-Shipment Receipt Update")
If Answer = vbCancel Then
    MsgBox "You pressed Cancel", vbExclamation, "Check Again!"
    
    Exit Sub
Else
With ws
  .Cells(lRow, 1).Value = Me.Oft.Value
  .Cells(lRow, 2).Value = Me.cType.Value
  .Cells(lRow, 3).Value = Me.pType.Value
  .Cells(lRow, 4).Value = Me.moProdbox.Value
  .Cells(lRow, 5).Value = Me.moProdbox.List(lPart, 1)
  .Cells(lRow, 6).Value = Me.sLine.Value
  .Cells(lRow, 7).Value = Me.conNum.Value
  .Cells(lRow, 8).Value = Me.bNum.Value
  .Cells(lRow, 9).Value = Me.vName.Value
  .Cells(lRow, 10).Value = Me.supName.Value
  .Cells(lRow, 11).Value = Me.purNum.Value
  .Cells(lRow, 12).Value = Me.invNum.Value
  .Cells(lRow, 13).Value = Me.invQty.Value
  .Cells(lRow, 14).Value = Me.certOrig.Value
  .Cells(lRow, 15).Value = Me.lotNum.Value
  .Cells(lRow, 16).Value = Me.ExpDate.Value
  .Cells(lRow, 17).Value = DateValue(Me.docRec.Value)
  .Cells(lRow, 18).Value = DateValue(Me.docFor.Value)
  .Cells(lRow, 19).Value = DateValue(Me.shptEta.Value)
  .Cells(lRow, 20).Value = DateValue(Me.tenUplan.Value)
MsgBox "Is this a Multiple Container entry BoL? If YES click YES else NO", vbInformation + vbYesNo, "BoL Entry"
[COLOR=red]If Response = vbYes Then[/COLOR]
[COLOR=red] Me.conNum.Value = ""
 Me.invQty.Value = ""
 Me.lotNum.Value = ""
 End If
 If Response = vbNo Then
 Me.cType.Value = ""
 Me.pType.Value = ""
 Me.moProdbox.Value = ""
 Me.sLine.Value = ""
 Me.conNum.Value = ""
 Me.bNum.Value = ""
 Me.vName.Value = ""
 Me.supName.Value = ""
 Me.purNum.Value = ""
 Me.invNum.Value = ""
 Me.invQty.Value = ""
 Me.certOrig.Value = ""
 Me.lotNum.Value = ""
 End If
[/COLOR]End With

With cs
Application.ScreenUpdating = False
  .Cells(CrOW, 1).Value = Me.Oft.Value
  .Cells(CrOW, 2).Value = Me.cType.Value
  .Cells(CrOW, 3).Value = Me.pType.Value
  .Cells(CrOW, 4).Value = Me.moProdbox.Value
  .Cells(CrOW, 5).Value = Me.moProdbox.List(lPart, 1)
  .Cells(CrOW, 6).Value = Me.sLine.Value
  .Cells(CrOW, 7).Value = Me.conNum.Value
  .Cells(CrOW, 8).Value = Me.bNum.Value
  .Cells(CrOW, 9).Value = Me.vName.Value
  .Cells(CrOW, 10).Value = Me.invQty.Value
  .Cells(CrOW, 11).Value = DateValue(Me.docRec.Value)
  .Cells(CrOW, 12).Value = DateValue(Me.docFor.Value)
  .Cells(CrOW, 13).Value = DateValue(Me.shptEta.Value)
  .Cells(CrOW, 14).Value = DateValue(Me.tenUplan.Value)
Application.ScreenUpdating = True
End With
MsgBox "You pressed YES!", vbInformation, "Sheet Updated"
End If
 
 
 Me.docRec.Value = Format(CDate(docRec.Value), "MM/DD/YYYY")
 Me.docFor.Value = Format(CDate(docFor.Value), "MM/DD/YYYY")
 Me.shptEta.Value = Format(CDate(shptEta.Value), "MM/DD/YYYY")
 Me.tenUplan.Value = Format(CDate(tenUplan.Value), "dd/mm/yyyy")
 Me.cType.SetFocus
 'Me.Spreadsheet1.Sheets("EXXONMOBIL").Range("A1:AJ10").Range("A1:AJ10").Value = ThisWorkbook.Worksheets("EXXONMOBIL").Range("A1:AJ10").Value

'ErrHandler:
    'If Err.Number = 13 Then
    'MsgBox "Oooppss!some fields are empty, please double check.", vbExclamation, "After receipt Update"
    'End If
End Sub

 

[wigi]

You do not fill the variable Response, so I am not sure how you though using it in that case.

Consider:

            If MsgBox("Is this a Multiple Container entry BoL? If YES click YES else NO", vbInformation + vbYesNo, "BoL Entry") = vbYes Then
                Me.conNum.Value = ""
                Me.invQty.Value = ""
                Me.lotNum.Value = ""
            Else
                Me.cType.Value = ""
                Me.pType.Value = ""
                Me.moProdbox.Value = ""
                Me.sLine.Value = ""
                Me.conNum.Value = ""
                Me.bNum.Value = ""
                Me.vName.Value = ""
                Me.supName.Value = ""
                Me.purNum.Value = ""
                Me.invNum.Value = ""
                Me.invQty.Value = ""
                Me.certOrig.Value = ""
                Me.lotNum.Value = ""
            End If

 

[doofusboy]

Also, you Dim Response as an Integer and then check to see if it's equal to "vbYes". So even if you made an assignment to Response you'd encounter issues.

 

[wigi]

Also, you Dim Response as an Integer and then check to see if it's equal to "vbYes". So even if you made an assignment to Response you'd encounter issues.

Why would you encounter issues? As you could see in the Immediate Window, vbYes is equal to 6 and vbNo is equal to 7.

 

[HYKE]

Hi WIKI,

Thanks for looking into the code and for providing an immediate solution. The code is now giving me the result I want. Thanks again!

HYKE