Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer data to form from Excel range upon loading of form.
Hi. I'd like to know if it's possible for a list box (or any type) to return
the value already in the excel cell "range" that is specified upon opening of the form. If I have a list of values (say 1,2,3,4,5), and have run the program previously, selecting "2", when I (re)open the form up, i'd like the box on the form to already have the "2" within it. I've tried various things with rowsource, etc, but can't seem to get this to work. Any Ideas please? (using excel2002) Regards, Rob. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer data to form from Excel range upon loading of form.
rowsource just identifies the data to place in the box. The value of the
Listbox or the Listindex would be used to specify the selection. If you want to unload the form and use the last selected value when you recreate it again, then you would need to store that information somewhere. One possibility is to use the controlsource to link it to a cell, then when you open the form, you can use the initialize event to set it to that value: Private Sub UserForm_Initialize() Dim rng as Range Set rng = Range(Me.ListBox1.ControlSource) If Not IsEmpty(rng) Then Me.ListBox1.Value = rng.Value End If End Sub -- Regards, Tom Ogilvy "Rob Crawford" wrote in message ... Hi. I'd like to know if it's possible for a list box (or any type) to return the value already in the excel cell "range" that is specified upon opening of the form. If I have a list of values (say 1,2,3,4,5), and have run the program previously, selecting "2", when I (re)open the form up, i'd like the box on the form to already have the "2" within it. I've tried various things with rowsource, etc, but can't seem to get this to work. Any Ideas please? (using excel2002) Regards, Rob. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer data to form from Excel range upon loading of form.
Tom,
thanks, you're a star. not only works, it's given me a few more ideas to go back and look at some old files with! "Tom Ogilvy" wrote: rowsource just identifies the data to place in the box. The value of the Listbox or the Listindex would be used to specify the selection. If you want to unload the form and use the last selected value when you recreate it again, then you would need to store that information somewhere. One possibility is to use the controlsource to link it to a cell, then when you open the form, you can use the initialize event to set it to that value: Private Sub UserForm_Initialize() Dim rng as Range Set rng = Range(Me.ListBox1.ControlSource) If Not IsEmpty(rng) Then Me.ListBox1.Value = rng.Value End If End Sub -- Regards, Tom Ogilvy "Rob Crawford" wrote in message ... Hi. I'd like to know if it's possible for a list box (or any type) to return the value already in the excel cell "range" that is specified upon opening of the form. If I have a list of values (say 1,2,3,4,5), and have run the program previously, selecting "2", when I (re)open the form up, i'd like the box on the form to already have the "2" within it. I've tried various things with rowsource, etc, but can't seem to get this to work. Any Ideas please? (using excel2002) Regards, Rob. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
transfer data from excel form to a spreadsheet | Excel Discussion (Misc queries) | |||
How to Transfer data from Text Form Fields in MS Word into Excel? | Excel Discussion (Misc queries) | |||
Form 2 Form data transfer | Excel Programming | |||
Loading Excel Data into a Form | Excel Programming | |||
Transfer multiple columns items form listbox to range | Excel Programming |