Userform data

I have three userforms, each of them getting their data from three different
worksheets. Most of the work I do is in a fourth worksheet where the three
userforms should pop up. To load each of these userforms, I used :

UserForm1.ListBox1.RowSource = "b7:b" & items_quantity

Obviously, this works as long as the active worksheet is that where the data
is located.

Is there any way of collecting this data referencing the worksheet where it
is located ( ie UserForm1.ListBox1.RowSource = sheet1.range("b7:b" &
items_quantity)). I tried that but it doesn't work.

Thanks for your help.

UserForm1.ListBox1.RowSource = Worksheets("Sheet1").Range("B7:B" &
items_quantity).Address(,,,True)


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Cordobes" <Cordobes@discussions.microsoft.com> wrote in message
news:1BA91537-BCCF-4372-987B-D66DB9B7ED79@microsoft.com...
> I have three userforms, each of them getting their data from three
different
> worksheets. Most of the work I do is in a fourth worksheet where the three
> userforms should pop up. To load each of these userforms, I used :
>
> UserForm1.ListBox1.RowSource = "b7:b" & items_quantity
>
> Obviously, this works as long as the active worksheet is that where the
data
> is located.
>
> Is there any way of collecting this data referencing the worksheet where
it
> is located ( ie UserForm1.ListBox1.RowSource = sheet1.range("b7:b" &
> items_quantity)). I tried that but it doesn't work.
>
> Thanks for your help.

Bob,

Thank you very much for the quick response. I trie what you suggested and it
is giving me this message <Subscript out of range>.

"Bob Phillips" wrote:

> UserForm1.ListBox1.RowSource = Worksheets("Sheet1").Range("B7:B" &
> items_quantity).Address(,,,True)
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Cordobes" <Cordobes@discussions.microsoft.com> wrote in message
> news:1BA91537-BCCF-4372-987B-D66DB9B7ED79@microsoft.com...
> > I have three userforms, each of them getting their data from three
> different
> > worksheets. Most of the work I do is in a fourth worksheet where the three
> > userforms should pop up. To load each of these userforms, I used :
> >
> > UserForm1.ListBox1.RowSource = "b7:b" & items_quantity
> >
> > Obviously, this works as long as the active worksheet is that where the
> data
> > is located.
> >
> > Is there any way of collecting this data referencing the worksheet where
> it
> > is located ( ie UserForm1.ListBox1.RowSource = sheet1.range("b7:b" &
> > items_quantity)). I tried that but it doesn't work.
> >
> > Thanks for your help.
>
>
>

You have to use the worksheet name that you actually have, Sheet1 was just
an example.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Cordobes" <Cordobes@discussions.microsoft.com> wrote in message
news:B2B78800-7213-48D2-8511-A22D4FFB557D@microsoft.com...
> Bob,
>
> Thank you very much for the quick response. I trie what you suggested and
it
> is giving me this message <Subscript out of range>.
>
> "Bob Phillips" wrote:
>
> > UserForm1.ListBox1.RowSource = Worksheets("Sheet1").Range("B7:B" &
> > items_quantity).Address(,,,True)
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Cordobes" <Cordobes@discussions.microsoft.com> wrote in message
> > news:1BA91537-BCCF-4372-987B-D66DB9B7ED79@microsoft.com...
> > > I have three userforms, each of them getting their data from three
> > different
> > > worksheets. Most of the work I do is in a fourth worksheet where the
three
> > > userforms should pop up. To load each of these userforms, I used :
> > >
> > > UserForm1.ListBox1.RowSource = "b7:b" & items_quantity
> > >
> > > Obviously, this works as long as the active worksheet is that where
the
> > data
> > > is located.
> > >
> > > Is there any way of collecting this data referencing the worksheet
where
> > it
> > > is located ( ie UserForm1.ListBox1.RowSource = sheet1.range("b7:b" &
> > > items_quantity)). I tried that but it doesn't work.
> > >
> > > Thanks for your help.
> >
> >
> >