Combobox List Lost On Closing Workbook

Hello old freinds, Simple combobox query:

step 1. open excel, control toolbar, add new combobox (named combobox1)
step 2. create new module, with the following code:

sub test
with sheets(1).combobox1
    .AddItem "123"
end with
end sub

step 3. run macro "test", combobox1 should be populated, save and close file.
step 4. re-open file, drop down on combobox1, no values to select from.

What am i missing here?

Re: Combobox List Lost On Closing Workbook

Thanks for the quick reply. Is there a solution that doesn't involve storing the entries? The combobox values I'd like to keep are generated with a before_save event, and the entries are variable (username + timestamp)

Cheers,

Re: Combobox List Lost On Closing Workbook

Hi Dave,

Thanks for your suggestion. Populating the combobox from workbook_open event works for initial entries, but I would like to save entries generated while workbook is open. Do you have another way to do this?

Re: Combobox List Lost On Closing Workbook

Something still not right, please see code below, which is generating a 438 run time error

Sub user_hist()
Dim rng As Range
Set rng = Sheets("tracking").Range("a2:a20")


ActiveSheet.Range("a" & ActiveSheet.Rows.Count).End(xlUp).Offset(1, 0).Value = Application.UserName & " " & Format$(Date, "mm-dd-yy") & " " & Time


With Sheets(1).ComboBox1
    .Style = fmStyleDropDownList
    .RowSource = rng.Address
End With
End Sub

Re: Combobox List Lost On Closing Workbook

Quote from Dave Hawley

No need to set the RowSource at run-time, just set it once at design-time.

Dave,

I'm struggling to do this, I don't have a userform, so where would I set this?

Thanks,