Re-Populate Listbox Contents

[f64]

I am employing a multipage userform to fill in an Excel spreadsheet. The contents of a listbox is assembled by using "AddItem", based on selections made in other listboxes. The list may contain one to four items. The user would like to save the spreadsheet and re-launch the userform, and the challenge is repopulating all the textboxes and listboxes in the form from data stored in the spreadsheet when the form is re-launched. Textboxes and Listboxes with fixed lists are not a problem, but the listbox with a variable list is a challenge. I suspect the listbox contents will have to be stored somewhere in the spreadsheet before the userform is closed, and then re-defined when the form is launched again. Anyone have a suggestion on how to re-populate the contents of the listbox when the form is re-launched, given a list that varies in length and content?

 

[bclt]

Make a new sheet (hidden with name "DataSheet") and save there the list items and the textboxes' texts. Make a sub() to save them:

Example:
Sub SaveDate()
With DataSheet
.cells(1,1)= ...
.cells(1,2)= ... 'etc
' save also the listbox; you don't need a loop because they are only four (max) as you said. BUT you don't know how many they will be, so:
for i=0 to list1.listcount-1
.cell(2,i+1)= list1.list(i)
next
End With
end sub

In this example i use col 1 for textboxes and col 2 for the list items

Then the other sub

Sub GetData()
With Data
Sheet1.Cells(??,??)= .cells(1,1)
Sheet1.Cells(??,??)= .cells(1,2) 'etc

Sheet1.Cell(??,??)= .Cells(2,1)
Sheet1.Cell(??,??)= .Cells(2,2)
Sheet1.Cell(??,??)= .Cells(2,3)
Sheet1.Cell(??,??)= .Cells(2,4) ' for all four, do not omit anything; if a cell is empty no problem
End With
End sub


Propably add a button to call the sub "getData" and one to Save the data



-ok?
-bclt