Hello,
I have this strange problem with my code and i just can't seem to figure out the issue.
The code below is situated in a userform and when a customer is selected from the combo-box drop-down the code checks whether the customer exists in my "customer database" which is one of the tabs on my excel workbook.
So if the customer exists then the code writes the customer into the active sheet (not the customer database sheet), and if the customer does not exist then the code prompts the user with the following: "This customer is not in the customer list. To automatically add this customer to the list click Yes." When "yes" is selected the code adds this customer to the customer database and adds the customer to the active worksheet.
The issue i am having is very strange, most times the customer gets added to the activesheet, but sometimes the customer does not get added.
i would really be grateful to anyone who can help me solve this problem.
This code is Before update event
Private Sub CustBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim B As String
Dim rcell As Long
Dim lr As Long
B = ActiveCell.Offset(0, 1).Value
Dim CUSTOMER As String
CUSTOMER = ActiveCell.Offset(1, 22).Value
Dim i As Integer
If Me.CustBox1.Value <> "" Then GoTo CheckEntry:
ActiveCell.Offset(1, 22).Value = CustBox1.Text
Exit Sub
CheckEntry:
For i = 0 To Me.CustBox1.ListCount - 1
If Me.CustBox1.List(i) = Me.CustBox1.Value Then: Exit Sub
ActiveCell.Offset(1, 22).Value = CustBox1.Text
Next i
Dim strPrompt As String
Dim strTitle As String
Dim iRet As Integer
strPrompt = CustBox1.Text & " is not in the customer list." & vbNewLine & vbNewLine & "To automatically add " & CustBox1.Text & " to the list click Yes."
strTitle = "Message from Microsoft"
iRet = MsgBox(strPrompt, vbYesNo, strTitle)
If iRet = vbNo Then
If Not CUSTOMER = "" Then
CustBox1.Text = CUSTOMER
ActiveCell.Offset(1, 22).Value = CustBox1.Text
Else
CustBox1.Text = ""
CheckBox1A = False
Me.CheckBox1.Enabled = False
Me.PriceBox1.Enabled = False
Me.FOB1.Enabled = False
Me.DEL1.Enabled = False
Me.CustBox2.Enabled = False
End If
Exit Sub
End If
If iRet = vbYes Then
Set wb = ThisWorkbook
Set wsCust = wb.Worksheets("CustomerList")
Dim Lrc As Long
Lrc = wsCust.Range("A" & Rows.Count).End(xlUp).Row
With Sheets("CustomerList").Range("A" & Lrc + 1)
.Value = CustBox1.Text
With Sheets("CustomerList").Range("A2:ZZ" & Sheets("CustomerList").Range("A" & Rows.Count).End(xlUp).Row)
.Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlNo
End With
End With
End If
CustBox1.Clear
Set CustListRng = wsCust.Range("A2", wsCust.Range("A" & Rows.Count).End(xlUp))
For Each Cel In CustListRng
CustBox1.AddItem (Cel.Value)
Next
ActiveCell.Offset(1, 22).Value = CustBox1.Text
End Sub
This event (which belongs to the same combo-box) is a change event
Private Sub CustBox1_Change()
If CustBox1.Text <> "" Then
Me.CheckBox1.Enabled = True
Me.PriceBox1.Enabled = True
Me.FOB1.Enabled = True
Me.DEL1.Enabled = True
CheckBox1A = True
Me.CustBox2.Enabled = True
If PriceBoxAll <> "" Then
PriceBox1 = PriceBoxAll
End If
If FOBBoxAll <> "" Then
FOB1 = FOBBoxAll
End If
If DelTimeBoxAll <> "" Then
DEL1 = DelTimeBoxAll
End If
End If
ActiveCell.Offset(1, 15).Value = OfferBox.Text
If PriceBoxAll <> "" Then
With ActiveCell.Offset(0, 20)
.Copy ActiveCell.Offset(1, 23)
.Copy ActiveCell.Offset(1, 20)
End With
End If
If FOBBoxAll <> "" Then
With ActiveCell.Offset(0, 17)
.Copy ActiveCell.Offset(1, 24)
.Copy ActiveCell.Offset(1, 17)
End With
End If
If DelTimeBoxAll <> "" Then
With ActiveCell.Offset(0, 18)
.Copy ActiveCell.Offset(1, 25)
.Copy ActiveCell.Offset(1, 18)
End With
End If
End Sub
Bookmarks