Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Code for listbox.rowsource?

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Private Sub CommandButton1_Click()

    For Each s In Worksheets("sheet2").range("a1:a100")
    If s.Value = ListBox1.Value Then
    x = s.Offset(0, 1).Value
    Sheets(x).Activate

    ListBox2.rowsource = Sheets(x).range("a1:i10000")

    End If
    Next s
    exit sub

    This code finds a sheet from a selection in listbox1 when the user clicks the commandbutton. Why cant I figure out why I cant get the range A1:I10000 of that sheet to display in listbox2 ????
    Please help

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    Todd,

    Off topic, more or less anyway.

    A ListBox housing 10,000 items amazes me. How do you use it?

    Aladin

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Its actually supposed to read range(a1:i100).
    Any ideas?

  4. #4
    New Member
    Join Date
    Aug 2011
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code for listbox.rowsource?

    I really would like that someone had replied to you, because I'm looking for the exact same thing

    Will continue to search the forum

  5. #5
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Code for listbox.rowsource?

    Try like this

    Code:
    UserForm1.ListBox1.RowSource = Worksheets("sheet1").Range("A1:I100").Address(external:=True)
    HTH, Peter
    Please test any code on a copy of your workbook.

  6. #6
    New Member
    Join Date
    Aug 2011
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code for listbox.rowsource?

    Wow!



    Thanks very much!

  7. #7
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,007
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Code for listbox.rowsource?

    You can also use List.
    Code:
    Listbox1.List = Sheets(x).Range("A1:I100").Value
    No real advantage I suppose.

    Doesn't link the range and listbox directly.
    If posting code please use code tags.

  8. #8
    New Member
    Join Date
    Aug 2011
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code for listbox.rowsource?

    Thanks!

    So, if I use list, when the data changes in the sheet, it will not change in the ListBox? That has some advantages for what I'm trying to do.

  9. #9
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,007
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Code for listbox.rowsource?

    That's right.

    The obvious disadvantage is that you'll need to take care of the transfer to the worksheet yourself.

    Mind you that could be an advantage as it'll give you more control.
    If posting code please use code tags.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •