CODEWITHSUNDEEP
excelvba
user3122928
user3122928

Reputation: 11

how to add item to a combobox on button click?

I want to add item to a combobox found in excel worksheet from text box which is located in the user form When button is clicked.i see the value added to combobox but it will become empty when I close and reopens the workbook.can any one help me handling this?

Thank u for you're fast response first

thank you both for your feedback and correction.let me make more clear my concern

Upvotes: 0

Views: 1516

Answers (2)

Aleksey F.
Aleksey F.

Reputation: 761

  1. Create a workbook and save as xlsm.
  2. On the first worksheet define user name as follows: Name: dn_cmb_items Range: =""
  3. Using the developer ribbon add an Excel (not ActiveX) combobox onto the Worksheet1 and set its list-by-range to dn_cmb_items
  4. Open VBA editor and add a user form to the workbook, name it as frm_add_cmb_item and set ShowModal to False.
  5. Drop a text box to the form and name it tb_item_text.
  6. Drop a button to the form, name it cmb_add and from its context menu choose View code. This creates the click event handler.
  7. Implement the handler as follows:
    Private Sub cmb_add_Click()
      Dim v_r As Range, v_n As Name
      Set v_n = Names("dn_cmb_items")
      If v_n.Value = "=""""" Then
        v_n.Value = "=" & Worksheets(1).Name & "!$A$1:$A$1"
        v_n.RefersToRange.Value = tb_item_text.Text
      Else
        Set v_r = v_n.RefersToRange
        Set v_r = v_r.Cells(v_r.Rows.Count + 1, 1)
        v_r.Value = tb_item_text.Text
        v_n.Value = "=" & Worksheets(1).Name & "!$A$1:" & v_r.Address(True, True)
      End If
    End Sub
  1. Drop onto the workshet a button, then create/set a macros in the workbook. Implement the created macros with the code frm_add_cmb_item.Show.
  2. In the VBA Editor from the Debug menu choose Compile. Then save the VBAProject as well as the workbook. That's all for the coding.
  3. Switch to the worksheet, show the form.
  4. Now when you enter some to the textbox, then click the cmb_add button, a new item will be added to the A column at the end thus changing the value of dn_cmb_items assigned to the combobox on the worksheet. See the screenshorts attached:

Initial state: Initial state

1 added: Add 1

2 added: Add 2

PS I have the ready workbook with all the code. Where should I upload it?

Upvotes: 1

Nishank
Nishank

Reputation: 475

Be specific with your question and always post the relevant code, so that it will become easy to solve it for others.

If you want to see the data while executing the userform, just write the required data in userform_activate or Initialize. before executing it will take the values and shows up in the combobox.

the input which you are taking from the worksheet just write those values in another worksheet so that whenever you open the workbook the values will not get erased.

Upvotes: 0

Related Questions