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.

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" <RobCrawford@discussions.microsoft.com> wrote in message
news:716F8626-C7C0-4B30-BD8F-F02EE5652E0B@microsoft.com...
> 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.
>

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" <RobCrawford@discussions.microsoft.com> wrote in message
> news:716F8626-C7C0-4B30-BD8F-F02EE5652E0B@microsoft.com...
> > 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.
> >
>
>
>