Updating TextBox in ComboBox Filled UserForm

[kalmaraz]

I am having trouble editing textbox values of a combobox filled user form. I have a worksheet we'll call customer_info that has rows of customer info(name, address, etc). On the userform, when I select the customer name from a combobox(named list range of customers), I use the Match function to find the row associated with the name and fill in the values of all the text boxes. This info is then used to populate a separate "quote" type sheet with the click of a button. I would like to be able to edit the textbox values before I populate the "quote" sheet if a customer info has changed, therefore also updating my cutomer info sheet. I have the userform working to the point of filling in the textbox values upon change of the combobox(selection of a name), but whenever I edit the text box and click the (add) button, the textbox value defaults back to the originally loaded values and does not update my sheet. Below is my userform code so you can see what I am doing. Any suggestions? Thank you.

Private Sub UserForm_Initialize()

EditButton.Enabled = False

End Sub


Private Sub CompNameComboBox_Change()

Dim quoteDB As Workbook
Dim custDB As Worksheet

Set quoteDB = Workbooks("Quote Sheet Database.xlsm")
Set custDB = Worksheets("Customer_Bank")

'Enable edit button
EditButton.Enabled = True

'Read Customer_Name
CustomerName = CompNameComboBox.Value
RowNum = Application.WorksheetFunction.Match(CustomerName, quoteDB.custDB.Range("Co_Name_List"), 0)
Label1.Tag = RowNum

'Transfer information into form
CompContactTextBox.Value = quoteDB.custDB.Cells(RowNum, 2).Value
Address1TextBox.Value = quoteDB.custDB.Cells(RowNum, 3).Value
Address2TextBox.Value = quoteDB.custDB.Cells(RowNum, 4).Value
CityTextBox.Value = quoteDB.custDB.Cells(RowNum, 5).Value
StateTextBox.Value = quoteDB.custDB.Cells(RowNum, 6).Value
ZIPTextBox.Value = quoteDB.custDB.Cells(RowNum, 7).Value
Phone1TextBox.Value = quoteDB.custDB.Cells(RowNum, 8).Value
Phone2TextBox.Value = quoteDB.custDB.Cells(RowNum, 9).Value
FaxTextBox.Value = quoteDB.custDB.Cells(RowNum, 10).Value
EmailTextBox.Value = quoteDB.custDB.Cells(RowNum, 11).Value


End Sub




Private Sub EditButton_Click()

Dim quoteDB As Workbook
Dim custDB As Worksheet

Set quoteDB = Workbooks("Quote Sheet Database.xlsm")
Set custDB = Worksheets("Customer_Bank")


'Set Row
RowNum = Label1.Tag

'Transfer information from form to database
quoteDB.custDB.Cells(RowNum, 1).Value = CompNameComboBox.Value
quoteDB.custDB.Cells(RowNum, 2).Value = CompContactTextBox.Value
quoteDB.custDB.Cells(RowNum, 3).Value = Address1TextBox.Value
quoteDB.custDB.Cells(RowNum, 4).Value = Address2TextBox.Value
quoteDB.custDB.Cells(RowNum, 5).Value = CityTextBox.Value
quoteDB.custDB.Cells(RowNum, 6).Value = StateTextBox.Value
quoteDB.custDB.Cells(RowNum, 7).Value = ZIPTextBox.Value
quoteDB.custDB.Cells(RowNum, 8).Value = Phone1TextBox.Value
quoteDB.custDB.Cells(RowNum, 9).Value = Phone2TextBox.Value
quoteDB.custDB.Cells(RowNum, 10).Value = FaxTextBox.Value
quoteDB.custDB.Cells(RowNum, 11).Value = EmailTextBox.Value


'Transfer Some Info form to Quote Sheet
Worksheets("Start_Here").Range("Customer_Name") = CompNameComboBox.Value
Address1 = Address1TextBox.Value
Worksheets("Formal_Quote").Range("Cust_Address_1") = Address1
Address2 = Address2TextBox.Value
Worksheets("Formal_Quote").Range("Cust_Address_2") = Address2
CityStateZip = CityTextBox.Value + ", " + StateTextBox.Value + " " + ZIPTextBox.Value
Worksheets("Formal_Quote").Range("City_State_Zip").Value = CityStateZip





Unload Me


End Sub




Private Sub CancelButton_Click()


Unload Me


End Sub

 

[kalmaraz]

Actually, I guess the code should look like this:

Private Sub UserForm_Initialize()

EditButton.Enabled = False

End Sub


Private Sub CompNameComboBox_Change()

Dim custDB As Worksheet

Set custDB = Workbooks("Quote Sheet Database.xlsm").Worksheets("Customer_Bank")

'Enable edit button
EditButton.Enabled = True

'Read Customer_Name
CustomerName = CompNameComboBox.Value
RowNum = Application.WorksheetFunction.Match(CustomerName, custDB.Range("Co_Name_List"), 0)
Label1.Tag = RowNum

'Transfer information into form
CompContactTextBox.Value = custDB.Cells(RowNum, 2).Value
Address1TextBox.Value = custDB.Cells(RowNum, 3).Value
Address2TextBox.Value = custDB.Cells(RowNum, 4).Value
CityTextBox.Value = custDB.Cells(RowNum, 5).Value
StateTextBox.Value = custDB.Cells(RowNum, 6).Value
ZIPTextBox.Value = custDB.Cells(RowNum, 7).Value
Phone1TextBox.Value = custDB.Cells(RowNum, 8).Value
Phone2TextBox.Value = custDB.Cells(RowNum, 9).Value
FaxTextBox.Value = custDB.Cells(RowNum, 10).Value
EmailTextBox.Value = custDB.Cells(RowNum, 11).Value


End Sub




Private Sub EditButton_Click()

Dim custDB As Worksheet

Set custDB = Workbooks("Quote Sheet Database.xlsm").Worksheets("Customer_Bank")


'Set Row
RowNum = Label1.Tag

'Transfer information from form to database
custDB.Cells(RowNum, 1).Value = CompNameComboBox.Value
custDB.Cells(RowNum, 2).Value = CompContactTextBox.Value
custDB.Cells(RowNum, 3).Value = Address1TextBox.Value
custDB.Cells(RowNum, 4).Value = Address2TextBox.Value
custDB.Cells(RowNum, 5).Value = CityTextBox.Value
custDB.Cells(RowNum, 6).Value = StateTextBox.Value
custDB.Cells(RowNum, 7).Value = ZIPTextBox.Value
custDB.Cells(RowNum, 8).Value = Phone1TextBox.Value
custDB.Cells(RowNum, 9).Value = Phone2TextBox.Value
custDB.Cells(RowNum, 10).Value = FaxTextBox.Value
custDB.Cells(RowNum, 11).Value = EmailTextBox.Value



'Transfer Some Info form to Quote Sheet
Worksheets("Start_Here").Range("Customer_Name") = CompNameComboBox.Value
Address1 = Address1TextBox.Value
Worksheets("Formal_Quote").Range("Cust_Address_1") = Address1
Address2 = Address2TextBox.Value
Worksheets("Formal_Quote").Range("Cust_Address_2") = Address2
CityStateZip = CityTextBox.Value + ", " + StateTextBox.Value + " " + ZIPTextBox.Value
Worksheets("Formal_Quote").Range("City_State_Zip").Value = CityStateZip


Unload Me


End Sub




Private Sub CancelButton_Click()


Unload Me


End Sub