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.

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.

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.