Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving listbox values to a sheet then repopulate
' 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. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.518 / Virus Database: 316 - Release Date: 11/09/2003 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving listbox values to a sheet then repopulate
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" -- Regards, Tom Ogilvy "Stuart" wrote in message ... ' 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. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.518 / Virus Database: 316 - Release Date: 11/09/2003 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving listbox values to a sheet then repopulate
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. "Tom Ogilvy" wrote in message ... 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" -- Regards, Tom Ogilvy "Stuart" wrote in message ... ' 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. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.518 / Virus Database: 316 - Release Date: 11/09/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.518 / Virus Database: 316 - Release Date: 11/09/2003 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving listbox values to a sheet then repopulate
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" -- Regards, Tom Ogilvy "Stuart" wrote in message ... 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. "Tom Ogilvy" wrote in message ... 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" -- Regards, Tom Ogilvy "Stuart" wrote in message ... ' 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. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.518 / Virus Database: 316 - Release Date: 11/09/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.518 / Virus Database: 316 - Release Date: 11/09/2003 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving listbox values to a sheet then repopulate
Many thanks, ... as suspected.
Regards. "Tom Ogilvy" wrote in message ... 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" -- Regards, Tom Ogilvy "Stuart" wrote in message ... 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. "Tom Ogilvy" wrote in message ... 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" -- Regards, Tom Ogilvy "Stuart" wrote in message ... ' 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. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.518 / Virus Database: 316 - Release Date: 11/09/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.518 / Virus Database: 316 - Release Date: 11/09/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.518 / Virus Database: 316 - Release Date: 11/09/2003 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Listbox Values | Excel Discussion (Misc queries) | |||
Using a single cell value to repopulate multiple cells | Excel Worksheet Functions | |||
Using a single cell value to repopulate multiple cells | Excel Discussion (Misc queries) | |||
Values in a MultiColumn Listbox | Excel Programming | |||
Sorting ListBox results or transposing ListBox values to other cells for sorting | Excel Programming |