?
Solved

Listbox values Auto Saved

Posted on 2011-03-05
8
Medium Priority
?
449 Views
Last Modified: 2012-05-11
I will be adding values to a list box at a click command button from textbox in a VBA UserForm

I want the values to be saved in the listbox until i edit the list even the next time i load the User Form

Now the listbox values are cleared completely when i load the userform every time

Please suggest me the approach
0
Comment
Question by:srikanthv2322
  • 4
  • 3
8 Comments
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 total points
ID: 35044923
You can do one of these things

1) Save the listbox values to a temp sheet and then load it from there the next time userform loads.

2)  Save the listbox values to a text file and then load it from there the next time userform loads.

Sid
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35044929
Or, save the listbox to a publicly-defined array, then on _initialize routine, load it back up.

Or, don't unload the listbox when the user exits (only at close of workbook or something), then when you show it again, it should still have its last settings.

Dave
0
 

Author Comment

by:srikanthv2322
ID: 35044933
Can anyone demonstrate with a piece of code
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Expert Comment

by:dlmille
ID: 35044934
I just tested my last suggestion.

See code in Userform code page (loads a listbox on initialization with sheet names):
Private Sub UserForm_Initialize()
Dim mySheet As Worksheet

    For Each mySheet In Application.Worksheets
        ListBox1.AddItem (mySheet.Name)
    Next mySheet
End Sub

Open in new window


And this code is triggered when you LOAD.  You only need to LOAD once, if you don't UNLOAD (until you're completely done with it).

I called the userform like this:
Sub Initial_Load()
    Load UserForm1
    UserForm1.Show
End Sub

then - later when you want to ressurrect the form - do this...
Sub After_Load()
    UserForm1.Show
End Sub
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35044940
David: I thought of the 1st option of saving it to an array but what if the listbox has 100 rows? We would be taxing the memory too much?

Sid
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35044942
See attached file, with the above implemented...


Dave
demo-Userform-r1.xlsm
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35044948
@Sid - perhaps.  Depends on how much data we're talking about.  I like keeping the userform around (again, that could get hefty IF there's many implementations - but if its a time-consuming query result that doesn't need to be reloaded and its not a huge memory dump of data, any of the solutions should suffice).

I personally don't think 100 rows of data that a user picks from as being a large amount of memory.  Pick List say 40 characters, 100 rows = 4kb RAM.

:)

Dave
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35044970
>>>Can anyone demonstrate with a piece of code

Here is a sample in support of what I said in my first post.

The data will be saved or retrieved from Sheet1. In realistic situation this sheet could be hidden if there is another sheet present in the workbook.

Sid
Listbox-Example.xls
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question