?
Solved

Listbox values Auto Saved

Posted on 2011-03-05
8
Medium Priority
?
446 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: 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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

771 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