Saving listbox values to a sheet then repopulate

  • Thread starter Thread starter Stuart
  • Start date Start date
S

Stuart

' Add the sorted, non-duplicated items to a ListBox
For Each Item In mstrList
frmName_Contractors.lbDataCode.AddItem Item
Next Item

frmName_Contractors.Show

at this point (first time through) the user is presented with
principally a 2 column listbox. Col 1 has the lbDataCode
values and the user types their required new names into
Col 2.

Whenever they run this code in future on that book, they
have to retype their required new names again, from scratch.

So after the first time through, could I save the values in Cols
1 and 2 to a range on a sheet in the book, and then check for
the existence of that range, and if it exists then populate the
listbox?

I would need to check the saved Col 1 data against the new
Col 1 entries, in case user had made any changes, and
I could prompt the user "Do you wish to use previous values?"
instead of automatically loading the old names.

This is the best I can think of. How would I save the listbox
values and then use them to reload the listbox in the future,
please?

Regards.
 
Dim varr as Variant
varr = frmName_Contractors.lbDataCode.List
with worksheets("DataStore")
.Range("A1").Resize(Ubound(varr,1)-lbound(varr,1)+1, _
Ubound(varr,2)-lbound(varr,2)+1) = varr
End With


to get it back
With Worksheets("DataStore")
frmName_Contractors.lbDataCode.List = _
.Range("A1").CurrentRegion.Value
End With

Don't overlook the FullStops/periods preceding the "Range"
 
Many thanks.

Will try and understand that!

One further question, please.

There is now a new sheet in the user's data workbook
(namely "DataStore"). I am running several
'For Each ws'
etc routines on this book. Do I have to use something
like: If Not .Name = "DataStore" throughout the routine,
or when I create "DataStore" can I set it to 'Hidden'.

Have never understood how 'hidden' works in code
situations as opposed to user's point of view. I'm
guessing it's never hidden to code?

Regards and thanks.
 
you can hide it, but it will still be in your for each loop, so you have to
exclude working with it.

using if Not .Name = "DataStore"
 
Many thanks, ... as suspected.

Regards.

Tom Ogilvy said:
you can hide it, but it will still be in your for each loop, so you have to
exclude working with it.

using if Not .Name = "DataStore"
Click to expand...
 
Back
Top