Adding to excel user form combobox

[Gimp]

hello - I have a combobox that has the source from D212. What I want
to acomplish is, if a user enters in something that is not found in the
dropdown, or D212 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?

 

[Guest]

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" & "" & NextRow & "")
Set DynamicArea = .Range("DynamicArea")
End With
ComboBox1.ListFillRange = "DynamicArea"
End Sub

Regards,
Kari J Keinonen

 

[merjet]

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" & iRow)
ComboBox1.RowSource = rng.Worksheet.Name & "!" & rng.Address
End Sub

Hth,
Merjet

 

[Gimp]

Thanks...Merjet, worked like a charm....