Reload combobox contents after saving file

[AnnieM]

Hi Experts,

I have a macro-enabled excel template with multiple combo boxes on a sheet.

My problem is that whenever I save and then reopen the file, the combo boxes are back to their original (unselected) content and I need them to re-open with the last selection.

My existing code is :

Private Sub Workbook_Open()
Dim arrString() As String
With Me
With Sheet1.cmbCompany
.AddItem "Select from List:"
.AddItem "2 : Company A"
.AddItem "3 : Company B"
.AddItem "8 : Company C"
.AddItem "7 : Company D"
.AddItem "1 : Company E"
.ListIndex = 0
End With
With Sheet1.cmbCustomer
.AddItem "Select from List or add Cust:"
.AddItem "Customer A"
.AddItem "Customer B"
.AddItem "Customer C"
.AddItem "Customer D"
.AddItem "Customer E"
.ListIndex = 0
End With

etc, etc.

I have tried to change this to a Workbook_New or Workbook_Initialize sub but then nothing works.

As you can probably tell I am a novice at this and so would appreciate your help enormously.

Thanks,

Anne

[ipisors]

Look into the ListIndex property. And, certainly don't set it to 0. :)

[snb]

The worksheets are meant to save data in, comboboxes/listboxes not.


Private Sub Workbook_Open()
Sheet1.cmbCompany.list=split("Select from List:_2: Company A_3 : Company B_8 : Company C_7 : Company D_1 : Company E","_")
Sheet1.cmbCustomer.list=split(replace("Select from List or add Cust:_~A_~B_~C_~D_~E","~","Customer "),"_")
End With

@ips
Nothing wrong with listindex 0 (= first item)

[ipisors]

My solution was based on my interpretation of the OP's question, which was that they needed to reopen (translate, drop-down) with the last selection. Thus, ListIndex.

Your solution appears to be based on the interpretation that they simply want to re-set the RowSource.

Who knows, guess the OP can clarify for us.

Getting back in the saddle after a few years, in answering VBA questions online, my skills at interpreting people's questions are probably in the toilet. :)

[snb]

@ipi

As you may have noticed, I didn't use rowsource (I never do).
I only say that what the OP wants isn't possible, since it is in contradiction to the design of ActiveX-controls.
The 'last selection' can't be saved in a combobox that is being populated when opening the workbook. If it can't retain it's own items, why should it be able to retain the 'last selection' ?

[AnnieM]

ipisors and snb,

Thank you both for getting back to me with your solutions and apologies if I wasn't clear in my post.

I don't understand quite why it works, but nevertheless commenting out the ListIndex line did the trick and now the sheet is reloading with the saved contents of the combo boxes. I guess this could prove to be a problem down the line if I need to reference specific items in other areas, but I will deal with that later if necessary.

Thanks again for your help

Anne

[ipisors]

Yep. Glad it worked. :)

Honestly, and in all deference to VBAX Guru, I wouldn't have thought it would have 'saved' that way either - but completely regardless of that, it's quite possible to provide a GUI for end users that 'saves' the value of the combobox, simply by custom programming it. For example, it can be saved to somewhere in the workbook when closed, and then re-populated with that ListIndex integer value, but yeah, ya have to code for it...

Glad we could help.
Isaac