Store values in Combo Box

[Guest]

Hi,

I am just wondering how could I store the values a user has inputed in the
combo box so that next time, the user run the macro again, he can click on
the combo box and find his last input in the drop-down list.

Thanks!

 

[merjet]

You don't say how the ComboBox is populated initially. But suppose
ComboBox1 is initially populated from a list in Sheet1!A1:An. If the
user adds a new item to ComboBox1, the following code will add the new
item to the list. DO NOT use RowSource in the Properties Window.

Private Sub UserForm_Activate()
Dim iEnd As Long
Dim rng As Range
iEnd = Sheets("Sheet1").Range("A1").End(xlDown).Row
Set rng = Sheets("Sheet1").Range("A1:A" & iEnd)
ComboBox1.RowSource = rng.Worksheet.Name & "!" & rng.Address
End Sub

Private Sub ComboBox1_Change()
Dim rng As Range
Set rng = Range(ComboBox1.RowSource)
If IsError(Application.Match(ComboBox1.Value, rng, 0)) = True _
And ComboBox1.Value <> "" Then
Set rng = rng.Resize(rng.Rows.Count + 1, 1)
rng(rng.Rows.Count, 1) = ComboBox1.Value
ComboBox1.RowSource = rng.Worksheet.Name & "!" & rng.Address
End If
End Sub

Hth,
Merjet

 

[Guest]

But what if the combo box is in a user form?! Like this user form will pop up
everytime the user run the macro.

 

[merjet]

But what if the combo box is in a user form?! Like this user form will pop up

everytime the user run the macro.

That is what I assumed. See my 1st line of code.

Merjet