Combo Box Items not sticking

[AlexanderBB]

In Workbook Opens event I'm adding items to some combo boxes.
When the Form is opened the items are there. Good.
But if I close the Form and open it again, they're gone.
I didn't expect this. It is normal behaviour or am I missing something?

 

[Artik]

Yes, this is normal behavior. You have combobox loading and form opening in the Workbook_Open event. On the other hand, by launching the form with, for example, the F5 key, you only open the form (combobox loading “stayed” in Workbook_Open).
You can use this code construction:
1. In the ThisWorkbook module:

Private Sub Workbook_Open()
    Call RunMyForm
End Sub

2. In the standard module:

Sub RunMyForm()
    With UserForm1
        .ComboBox1.List = Worksheets("Sheet1").Range("A1:A5").Value
        
        .Show
    End With
End Sub

If you want to reopen the form along with loading the combobox you run the RunMyForm procedure.

Artik

 

[AlexanderBB]

Thanks for confirming that @Artik. I'm trying some new (for me) stuff. I presume the same apples to any data in Form controls,. It will need to be re-applied every time the form is opened?
My aim is not to show the Form from Workbook open, just prepare it to Open with data loaded.

 

[RoryA]

It sounds like you should probably be using the Userform_Initialize event for your code then.

 

[AlexanderBB]

Thanks @RoryA, I'll try that and see results.

The Form holds various options like some Form properties and file paths.
They're kept in a db Table with fields for default values and user-set values.
In workbook open, Excel connects to the DB and loads user values, or if they're missing the default values into a class.
This lets me see/use them anywhere in the workbook. The User can opens the Form, see the data and make any changes This seem ok so far?

I've no example of this sort of this so have to work it out with trial and error. If something was changed I was updating the source db, but realised this could mean, for a temporary change, a later restore. My plan now is to try is update the db only in workbook before close, comparing the initial values against the current class ones.
So I think what you're advising is use Form initialize to write the values (from the class), and they'll be present when the Form is opened. When closed it compares/updates the class.

What I'm seeing is the Form not showing data, perhaps it must be restored first before Open.
I tried setting a Form Object, but that got an error about losing something, so guess that's not the right approach.

Will plug on with Initialize.. all good fun, thanks.