Add & Edit combobox and textbox linked with VBA in excel

Question

How to add or edit combo box values directly in following code? When writing a text in a combo box, show error "worksheetfunction" And I can not add a new code.

Private Sub UserForm_Click()
    Dim xRg As Range


Private Sub UserForm_Initialize()
    Set xRg = Worksheets("Sheet1").Range("A2:B8")
    Me.ComboBox1.List = xRg.Columns(1).Value
End Sub


Private Sub ComboBox1_Change()
    Dim xRg As Range
    Set xRg = Worksheets("Sheet1").Range("A2:B8")
    Me.TextBox1.Text = Application.WorksheetFunction _
        .VLookup(Me.ComboBox1.Value, xRg, 2, False)
End Sub

Spacing/indenting your code helps to easier identify problems... such as the fact that you've included 2 and a half procedures in your question. (see [mcve]) — ashleedawg, Oct 02 '18 at 08:58
I've got a few posts about using controls including [this one](https://stackoverflow.com/a/50144021/8112776) which includes example code for adding and manipulating combo boxes. Also see [this](https://stackoverflow.com/a/49263001/8112776) and [this](https://stackoverflow.com/a/48957591/8112776) and [this](https://stackoverflow.com/a/50318264/8112776) . :-) — ashleedawg, Oct 02 '18 at 09:04
Possible duplicate of [Overview of differences between Form Controls and ActiveX Controls in Excel](https://stackoverflow.com/questions/50144020/overview-of-differences-between-form-controls-and-activex-controls-in-excel) — Luuklag, Oct 02 '18 at 09:19

score 0 · Accepted Answer · answered Oct 02 '18 at 14:06

To add new items to a combo box list:

In this example, the combo box "ComboAdd" is on a form and is linked to data in "F" column:

Dim NewValueRange As String NewValueRange = "F" & Range("F1048576").End(xlUp).Row + 1 Range(NewValueRange) = UserForm1.ComboAdd.Value

Add & Edit combobox and textbox linked with VBA in excel

1 Answers1