Add value into userform Combobox & also on worksheet Table

[ipbr21054]

Morning.
I have a worksheet called INV
I open the userform shown below which then allows the user to select the drop down & select a value.
In this example, ComboBox1 is what we need to look at, its the one labelled "ENTER KEY TYPE BELOW"
The value in question isnt at present in the drop down list.
However the value is shown in TextBox3 labeled "VALUE FROM WORKSHEET"

The code will be added to CommanButton1 "COPY & ADD NEW VALUE"

It should work like this,
Copy the value in TextBox3 to ComboBox1
Also add this value to my worksheet Table
The sheet which has the table is called INFO
It is Table38 =MCKEYS
Thats it.

Next time the user opens ComboBox1 this new value will be present

Currently table is Row 1 Header & Values are Row 2 - 12

 

[DanteAmor]

Hi @ipbr21054

I added a couple of validations, you didn't ask for it, but that way the solution is more elegant.

Private Sub CommandButton1_Click()
  Dim tbl As ListObject
  Dim newRow As ListRow
  Dim f As Range
  
  Set tbl = Sheets("INFO").ListObjects("Table38")
  
  If TextBox3.Value = "" Then
    MsgBox "Enter new value"
    Exit Sub
  End If
  
  Set f = tbl.Range.Find(TextBox3.Value, , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    MsgBox "The value already exists"
    Exit Sub
  End If
  
  Set newRow = tbl.ListRows.Add
  newRow.Range(1) = TextBox3.Value
  
  ComboBox1.List = tbl.Range.Value
End Sub

 

[ipbr21054]

Hi,
I run that code but i get a message Run time Error 70, Permission Denied.
When i debug it i see this line below in Yellow.

 ComboBox1.List = tbl.Range.Value

But then when i go to check the Table the value is added ?

See photos

 

[DanteAmor]

I run that code but i get a message Run time Error 70, Permission Denied.

You are probably loading combobox1 with the "Rowsource" property.
In the future, try to put all the surroundings you have here. All your code, the properties you are using in each control, textbox, combobox, etc. Since we can't guess what you have.


Then try:

Private Sub CommandButton1_Click()
  Dim tbl As ListObject
  Dim newRow As ListRow
  Dim f As Range
  
  Set tbl = Sheets("INFO").ListObjects("Table38")
  
  If TextBox3.Value = "" Then
    MsgBox "Enter new value"
    Exit Sub
  End If
  
  Set f = tbl.Range.Find(TextBox3.Value, , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    MsgBox "The value already exists"
    Exit Sub
  End If
  
  Set newRow = tbl.ListRows.Add
  newRow.Range(1) = TextBox3.Value
  ComboBox1.RowSource = ""
  ComboBox1.Clear
  ComboBox1.List = tbl.Range.Value
End Sub

 

[ipbr21054]

That did the trick.Thanks very much