Solved

Listbox values Auto Saved

Posted on 2011-03-05
8
441 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 500 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 41

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 41

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 41

Expert Comment

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


Dave
demo-Userform-r1.xlsm
0
 
LVL 41

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

803 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