Solved

Listbox values Auto Saved

Posted on 2011-03-05
8
445 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 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 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
Technology Partners: 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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 will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

707 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