Userform Data entry "Update" setting wrong values when editing an entry

65 views Asked by At

So I've got a userform for entering and looking up customer information, Everythign seems to currently work, except for the "Update" Button. When utilizing the update button, its changing my city and state values to my zipcode value, aswell as adjusting whatever else was changed. City and state should not be changing to my zipcode value. city and state values are populated utilizing a vlookup within a zipcodedata tab.

Below is the code for my update button:


Private Sub UpdateButton_Click()

If Me.CustID.Value = "" Then
MsgBox "Select customer record to update!"
Exit Sub
End If
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Customer Database")
Dim Selected_Row As Long
Selected_Row = Application.WorksheetFunction.Match(CLng(Me.CustID.Value), ThisWorkbook.Worksheets("Customer Database").Range("A:A"), 0)
'Validations-----------------------------------------------------
If Me.CustomerName.Value = "" Then
MsgBox "Plese enter the customers name!", vbCritical
Exit Sub
End If
'-------
If Me.CustomerBusiness.Value = "" Then
MsgBox "Plese enter the customers business!", vbCritical
Exit Sub
End If
'-------
If Me.CustomerAddress.Value = "" Then
MsgBox "Plese enter the customers address!", vbCritical
Exit Sub
End If
'-------
If Me.CustomerPhone.Value = "" Then
MsgBox "Plese enter the customers phone number!", vbCritical
Exit Sub
End If
'----------------------------------------------------------------
sh.Range("B" & Selected_Row).Value = Me.CustomerName.Value
sh.Range("C" & Selected_Row).Value = Me.CustomerBusiness.Value
sh.Range("D" & Selected_Row).Value = Me.CustomerAddress.Value
sh.Range("E" & Selected_Row).Value = Me.City.Value
sh.Range("F" & Selected_Row).Value = Me.State.Value
sh.Range("G" & Selected_Row).Value = Me.Zipcode.Value
sh.Range("H" & Selected_Row).Value = Me.CustomerPhone.Value
sh.Range("I" & Selected_Row).Value = Me.CustomerEmail.Value
sh.Range("J" & Selected_Row).Value = Me.MachineSerial1.Value
sh.Range("K" & Selected_Row).Value = Me.MachineSerial2.Value
sh.Range("L" & Selected_Row).Value = Me.MachineSerial3.Value

'----------------------------------------------------------------
Me.CustomerName.Value = ""
Me.LCaseName.Value = ""
Me.CustomerBusiness.Value = ""
Me.CustomerAddress.Value = ""
Me.Zipcode.Value = ""
Me.City.Value = ""
Me.State.Value = ""
Me.CustomerPhone.Value = ""
Me.CustomerEmail.Value = ""
Me.MachineSerial1.Value = ""
Me.MachineSerial2.Value = ""
Me.MachineSerial3.Value = ""
Me.CustID.Value = ""
'----------------------------------------------------------------
Call Refresh
End Sub

Here is the code for populating the City and State text boxes:

Private Sub Zipcode_Change()
Dim rng As Range
Dim a As Integer

On Error Resume Next
a = 0
Set rng = Worksheets("ZipCodeData").Range("ZipCodes")
Lastrow = Worksheets("ZipCodeData").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To Lastrow
If Me.Zipcode = Worksheets("ZipCodeData").Cells(i, 1).Value Then
a = a + 1
End If
Next
If a >= 1 Then
Me.City.Value = Application.WorksheetFunction.VLookup(Zipcode.Value, rng, 2, 0)
Me.State.Value = Application.WorksheetFunction.VLookup(Zipcode.Value, rng, 3, 0)
End If
If a = 0 Then
Me.City.Value = ""
Me.State.Value = ""
End If
End Sub

I've tried commenting out my "Zipcode_Change" sub, as I thought perhaps it was having issues interpreting the vlookup values when utilizing the update feature. I have a "add" button (which is identical to the update button, except the Selected_Row is a Last_Row. The "Add" button functions and works as intended, so I recopied my "Add" button code and readjusted it for the update button, and the issue persists.

0

There are 0 answers