Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Editable Combox to add new data to table

0

I'm pretty good at coding but this is one that has always got me.  This works fine to view the values when a value is selected from the userform combobox. 

The combobox is on a userform.

I need to be able to add new information in all the textboxes (including combobox) and it add it to the last row in the worksheet.

Can someone help me please?

[CODE]Private Sub cboCustNo_Change()
Dim myRange As Range, f As Range

    Set myRange = Worksheets("Customer_Info").Range("A2:K50")

    Set f = myRange.Find(What:=cboCustNo.Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False) '<--| try and find combobox selected value
    If f Is Nothing Then
        cboCustNo.Value = ""
    Else '<--| ... otherwise...
        cboCustNo.Value = f.Offset(, 0)
        txtCoName.Value = f.Offset(, 1)
        txtContNm.Value = f.Offset(, 2)
        txtAdrs1.Value = f.Offset(, 3)
        txtAdrs2.Value = f.Offset(, 4)
        txtSteNo.Value = f.Offset(, 5)
        txtCity.Value = f.Offset(, 6)
        txtState.Value = f.Offset(, 7)
        txtZipCode.Value = f.Offset(, 8)
        txtPhoneNo.Value = f.Offset(, 9)
        txtFaxNo.Value = f.Offset(, 10)

    End If
End Sub


Private Sub UserForm_Initialize()

    Call Reset
    cboCustNo.List = Sheets("Customer_Info").Range("A1:A50").Value


End Sub
/CODE]

excelmacros-vbatext-manipulation
MS Office Version: 2019
posted by: Divinedar (rep: 20) on Thu Jan 12, 2023 at 3:13 pm
Answer
Discuss

Discussion

Hi Divinedar and welcome to the Forum. 

Please edit your question to attach a representative Excel file using the Add Files... button (so we don't need to recreate your UserForm etc.). We should then be able to give you specific help. 
John_Ru (rep: 6792) Jan 12, '23 at 4:23 pm
There are several things I want to do.

1.  Be able to add in a new customer id through the combobox and add new information in the textboxes then it's inserted into the worksheet.

2.  Also need to be able the see the list in the combobox even though I'm adding a new item.

3. Eventually my next move is to make a product table quoted or purchased by the customer that will give a history for the customer plus add to the product worksheet. This is a vision later.

Where would i call the "Submit" procedure?  Would it go to the end of here "Private Sub cboCustNo_Change".

Just working one step at a time to make sure of the capability of adding the customer information.

Thank you for your help.
Divinedar (rep: 20) Jan 12, '23 at 5:34 pm
Add to Discussion

Answers

0
Selected Answer

Divinedar

In the attached revised file, I changed a few things- firstly (in bold):

Private Sub UserForm_Initialize()

    Call Reset
    '### corrected this line
    cboCustNo.RowSource = "=Customer_Info!A2:A50"


End Sub

(REVISION- the above corrects my earlier version) then for your cited code, 

Private Sub cboCustNo_Change()
Dim myRange As Range, f As Range

    Set myRange = Worksheets("Customer_Info").Range("A2:K50")

    Set f = myRange.Find(What:=cboCustNo.Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False) '<--| try and find combobox selected value
    If f Is Nothing Then
        cboCustNo.Value = "TBC (new Customer)" '### changed this
    Else '<--| ... otherwise...
        cboCustNo.Value = f.Offset(, 0)
        txtCoName.Value = f.Offset(, 1)
        txtContNm.Value = f.Offset(, 2)
        txtAdrs1.Value = f.Offset(, 3)
        txtAdrs2.Value = f.Offset(, 4)
        txtSteNo.Value = f.Offset(, 5)
        txtCity.Value = f.Offset(, 6)
        txtState.Value = f.Offset(, 7)
        txtZipCode.Value = f.Offset(, 8)
        txtPhoneNo.Value = f.Offset(, 9)
        txtFaxNo.Value = f.Offset(, 10)

    End If
End Sub

so if you're don't pick an existing number (e.g. Q23002) from the pull-down but press a space say, that new message appears in the but haven't done that.

If you then fill in the other data fields and click the Submit button, this revised code will run: 

Sub Submit()

    Dim sh As Worksheet
    Dim iRow As Long
    Dim myRange As Range, f As Range ' ### added these
    Set sh = ThisWorkbook.Sheets("Customer_Info")

    iRow = [Counta(Customer_Info!A:A)] + 1 ' moved this

    'If frmForm.cboCustNo.Value <> "" Then ' changed this

    ' ### added this
    Set myRange = Worksheets("Customer_Info").Range("A2:A50")
    ' changed this to add form number
    Set f = myRange.Find(What:=frmForm.cboCustNo.Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False) '<--| try and find combobox selected value

    If Not f Is Nothing Then
        iRow = f.Row
    End If


    With sh
        ' replaced this
        '.Cells(iRow, 1) = "=Row()-1" 'Dynamic Serial Number
        .Cells(iRow, 1) = "Q23" & Format(iRow - 1, "000") 'New Dynamic Serial Number
        .Cells(iRow, 2) = frmForm.txtCoName.Value
        .Cells(iRow, 3) = frmForm.txtContNm.Value
        .Cells(iRow, 4) = frmForm.txtAdrs1.Value
        .Cells(iRow, 5) = frmForm.txtAdrs2.Value
        .Cells(iRow, 6) = frmForm.txtSteNo.Value
        .Cells(iRow, 7) = frmForm.txtCity.Value
        .Cells(iRow, 8) = frmForm.txtState.Value
        .Cells(iRow, 9) = frmForm.txtZipCode.Value
        .Cells(iRow, 10) = frmForm.txtPhoneNo.Value
        .Cells(iRow, 11) = frmForm.txtFaxNo.Value
        '.Cells(iRow, 12) = [Text(Now(), "DD-MM-YYYY HH:MM:SS")]
    End With


End Sub

In particular, the line:

        .Cells(iRow, 1) = "Q23" & Format(iRow - 1, "000") 'New Dynamic Serial Number

will made a hard-coded consecutive value like Q23005 in column A of worksheet Customer_Info (and you'll see I added bogus details for that there via the UserForm). plus the UserForm details will display then new line.

(Note: If I made any other changes not listed above, I commented them in the code)

Hope this helps..

posted by: John_Ru (rep: 6792) on Fri Jan 13, 2023 at 12:47 pm
Discuss

Discussion

Thank you.  I'm working on it now and I'll let you know.
Divinedar (rep: 20) Jan 13, '23 at 2:59 pm
Perfect!!!! Thank you I understand
Divinedar (rep: 20) Jan 13, '23 at 3:04 pm
Great! Thanks for selecting my answer, Divinedar. 
John_Ru (rep: 6792) Jan 13, '23 at 3:38 pm
What if I wanted the userform load that the next avaliable Customer Number appears in the Combobox but if you don't want to use and just want to select a customer it gives you what you select. But it also gives you the next avaliable number when lauch the userform again. In other words it resets to still use the next available customer number.

Private Sub cboCustNo_Change()
Dim myRange As Range, f As Range

    Set myRange = Worksheets("Customer_Info").Range("A2:K50")

    Set f = myRange.Find(What:=cboCustNo.Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False) '<--| try and find combobox selected value
    If f Is Nothing Then
        cboCustNo.Value = "TBC (new Customer)" '### changed this
    Else '<--| ... otherwise...
        cboCustNo.Value = f.Offset(, 0)
        txtCoName.Value = f.Offset(, 1)
        txtContNm.Value = f.Offset(, 2)
        txtAdrs1.Value = f.Offset(, 3)
        txtAdrs2.Value = f.Offset(, 4)
        txtSteNo.Value = f.Offset(, 5)
        txtCity.Value = f.Offset(, 6)
        txtState.Value = f.Offset(, 7)
        txtZipCode.Value = f.Offset(, 8)
        txtPhoneNo.Value = f.Offset(, 9)
        txtFaxNo.Value = f.Offset(, 10)

    End If
End Sub
Divinedar (rep: 20) Jan 13, '23 at 3:50 pm
Also when you click on the Combobox the first customer number is like stuck there and when you click on the it the information doesn't fill the textboxes.  I'm using the exact file you sent me.
Divinedar (rep: 20) Jan 13, '23 at 3:59 pm
Hi again Divinedar. Sorry but I only just spotted your comments (since I missed the alert somehow and don't get email notifications of comments, despite asking Don to fix that).

I'll try to look at your comments tomorrow if I get chance (I can't do anything related to macros while I'm just on my mobile phone) but the *next available customer number" thing looks like a new requirement (and we like to close off questions and add fresh questions if needed, not add new bits time and time again under an initial question).
John_Ru (rep: 6792) Jan 17, '23 at 2:52 pm
Ok thank you.  I didn't get a notification either.  Should I add a new question once I select an answer?  If so that'll work too. 
Divinedar (rep: 20) Jan 17, '23 at 3:29 pm
I'd suggest that if your original question isn't answered or there's a problem with the solution, ask in that thread.

If you realise you'd like something different (an improvement say) then ask a new question (linking back to the original if necessary). 
John_Ru (rep: 6792) Jan 17, '23 at 5:49 pm
Divinedar- please see my revised Answer/ file, which corrects the "stuck" first number.   All I had to do was change the first address in the initialise macro: 
Private Sub UserForm_Initialize()
 
    Call Reset
    '### corrected this line
    cboCustNo.RowSource = "=Customer_Info!A2:A50"
 
 
End Sub
Hope this now works well for you.   Please ask a new question if you want to pursue the other item.
John_Ru (rep: 6792) Jan 18, '23 at 7:08 am
Ok thank you. I will. I'm still not getting notifications either.
Divinedar (rep: 20) Jan 18, '23 at 1:54 pm
Okay Divinedar. Just to be clear, are you NOT getting notification emails but you DO see Alerts if you visit the website (and can follow them) ? If you click on your User Name (hyperlink) do you see these settings? 

  
Forum Settings
Email updates when replies are made to your posts: On 
Email updates when new questions are asked in the forum: On 

(There will be Update buttons alongside the On). 

Let me know please and I'll check with Don (who owns the site) . Bear in mind that his recent Forum newsletter email said the website will have a major overall soon.  Your answer might mean the Notification portion is tested / improved. Thanks in advance.   
John_Ru (rep: 6792) Jan 18, '23 at 2:14 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login