VBA UserForm prepopulated selection list with Add choice

[LNG2013]

In VBA I want to create a UserForm that displays a prepopulated list (Sheet2 Column B) in a Dropdown but also displays an "add" choice at the bottom of the list. If the Add choice is selected it will make a new field appear below the Dropdown, or maybe ungray one. The user can then fill in the field. The result will now be permanately be added to the list on Sheet 2 Column B.

 

[rosenbe]

displays a prepopulated list

Assuming a UserForm with a ComboBox called ComboxBox1:

Private Sub UserForm_Initialize()
  Dim rng As Range
  Dim lRow As Long
  lRow = 1
  Set rng = Sheet2.Range("B" & lRow)
  While rng.Value <> vbNullString
    ComboBox1.AddItem rng.Value
    lRow = lRow + 1
    Set rng = Sheet2.Range("B" & lRow)
  Wend
  LastRow = lRow ' LastRow would be a private UserForm variable
  ComboBox1.AddItem ("Add...")
End Sub

Assuming we also have a TextBox called TextBox1 which will by default be disabled.

Private Sub ComboBox1_Change()
  If ComboBox1.Text = "Add..." Then TextBox1.Enabled = True
End Sub

Then to add what we have entered into TextBox1 (this can be handle of number of ways, but for simplicity sake, I will assume the user, once done with the addition, will simply leave the TextBox; tab out).

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  Sheet2.Range("B" & LastRow).Value = TextBox1.Text
  LastRow = LastRow + 1
  TextBox1.Text = vbNullString
  TextBox1.Enabled = False
End Sub

This is meant to give you a starting point, hope it helps.