Vb code help needed

[Guest]

I have a combobox in worksheet2= (Users) and the dynamic list=(PickerList)
for it is in worksheet3=(SetUp) . Currently i have the combobox coded so that
the current active cell value becomes the value of the combobox , if a
selection is not in the combobox the user can select the word "add" from the
list and an input box
will arrive for them to enter the non existing text. all is well and working
as intended , except for the fact i would like to know if it is possible to
get there entry in the inputbox to add itself to the dynamic list ?
Here is the code currently being used,

Private Sub ComboBox1_Change()
ActiveCell.Value = ComboBox1.Value
If ComboBox1.Value = "Add" Then
res = InputBox("ADD NAME")
If res <> "" Then
ComboBox1.Value = res


End If
End If



End Sub
(lol)please keep in mind i'm no expert in vb code.
Thanks in advance to anyone willing to help

 

[Guest]

Use additem with no equal sign as shown below

Private Sub ComboBox1_Change()
ActiveCell.Value = ComboBox1.Value
If ComboBox1.Value = "Add" Then
res = InputBox("ADD NAME")
If res <> "" Then
ComboBox1.additem res


End If
End If



End Sub

 

[Guest]

I tried this , but get an Run-time error"70", permission denied . My dynamic
list name range is typed in as this
=OFFSET(SetUp!$A$1,0,0,CountA(SetUp!$A:$A),1)
I think this is the correct way in order to let the list grow as name's are
added.
Not sure why i'm getting permission denied.

 

[Guest]

I would use a worksheet_change functtion. When items are added in sheet 3
have the worksheet_change function add the item to the combobox.