Listbox values Auto Saved

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
srikanthv2322Asked:
Who is Participating?
 
SiddharthRoutConnect With a Mentor Commented:
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
 
dlmilleCommented:
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
 
srikanthv2322Author Commented:
Can anyone demonstrate with a piece of code
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
dlmilleCommented:
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
 
SiddharthRoutCommented:
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
 
dlmilleCommented:
See attached file, with the above implemented...


Dave
demo-Userform-r1.xlsm
0
 
dlmilleCommented:
@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
 
SiddharthRoutCommented:
>>>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.