Adding to excel user form combobox

  • Thread starter Thread starter Gimp
  • Start date Start date
G

Gimp

hello - I have a combobox that has the source from D2:D12. What I want
to acomplish is, if a user enters in something that is not found in the
dropdown, or D2:D12 that a yes no msg pops asking if they want to add
it, find D13 and add the text there so it's available later...make
sense?
 
Hi!

I hope that next code help you.

Private Sub ComboBox1_GotFocus()
Dim DynamicArea As Range
Dim NextRow As Long
Dim FirstRow As Long
With ThisWorkbook.Sheets(1)
NextRow = Cells(Rows.Count, "D").End(xlUp).Row
.Names.Add Name:="DynamicArea", RefersTo:= _
.Range("D2" & ":D" & NextRow & "")
Set DynamicArea = .Range("DynamicArea")
End With
ComboBox1.ListFillRange = "DynamicArea"
End Sub

Regards,
Kari J Keinonen
 
Private Sub ComboBox1_AfterUpdate()
Dim iRow As Long
Dim iRtn As Integer
iRtn = MsgBox("Keep this?", vbYesNo)
If iRtn = 6 Then
iRow = Sheets("Sheet1").Range("D65536").End(xlUp).Row
Sheets("Sheet1").Range("D" & iRow + 1) = ComboBox1.Value
UserForm_Activate
End If
End Sub

Private Sub UserForm_Activate()
Dim rng As Range
Dim iRow As Long
iRow = Sheets("Sheet1").Range("D65536").End(xlUp).Row
Set rng = Sheets("Sheet1").Range("D2:D" & iRow)
ComboBox1.RowSource = rng.Worksheet.Name & "!" & rng.Address
End Sub

Hth,
Merjet
 
Back
Top