Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Listbox Rowsource Property (97 SR2)

    I'm not sure if it's possible, that's why I'm asking you folks:

    Is there any way to specify a rowsource property for non-congruent columns? For Example, I want my list box to display row K, then row B, then row H. Can it be done?

    I know I can specify for a range, but I have no idea how I might accomplish this, aside from re-organizing my entire sheet, which I am reluctant to do. <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Listbox Rowsource Property (97 SR2)

    The RowSource property does not seem to like non-contiguous ranges. However you can give a range name to a non-contiguous range, and rather that use the Rowsource property, loop through the range and use the AddItem method too achieve what you want. Assume you have assigned the name SourceRange to your non-contiguous data the following UserForm initialize code should work for the control ListBox1 <pre> Private Sub UserForm_Initialize()
    Dim oCell As Range
    With ListBox1
    For Each oCell In Range("SourceRange")
    .AddItem oCell.Value
    Next
    End With
    End Sub</pre>

    You can assign the range name by selecting all the areas involved and doing Insert | Name | Define etc, or it can be done in code if it is not fixed for all time.

    Andrew C

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Listbox Rowsource Property (97 SR2)

    Drk, assuming you are filling a userform listbox from columns K, B and H (you did mean columns, right?), and you want to display them as three columns, here is the code:<pre>Option Explicit

    Private Sub UserForm_Initialize()
    Dim i As Integer
    Dim c As Range
    With ListBox1
    .ColumnCount = 3
    .ColumnWidths = "25;25;25"
    i = 0
    For Each c In Intersect(Sheets("Sheet1").Columns("K:K"), _
    Sheets("Sheet1").UsedRange)
    .AddItem c.Value
    .List(i, 1) = c.Offset(0, -9).Value ' -9 = K backto B
    .List(i, 2) = c.Offset(0, -3).Value ' -3 = K backto H
    i = i + 1
    Next
    End With
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox Rowsource Property (97 SR2)

    Interesting...

    I'm seeing some very unfamiliar code here, do you think you could break down how it works?

    There are many more columns than K, B, & H, so i'm a little confused as to how I might add these columns based on this code... I think it's the Offset that's got me baffled... <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Listbox Rowsource Property (97 SR2)

    Drk, Offset is ugly and always confuses me too. I only used it so I didn
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox Rowsource Property (97 SR2)

    Great! Now I see what's going on...

    However, before I update the list box, I am doing the following:

    Sorting the data (in the worksheet) according to a criteria field specified in a control by the user,
    Running a search for the criteria
    specifying the search's resulting row number (as the starting point)
    Running a reverse search (xlPrevious) for the criteria
    specifying the search's resulting row number (as the ending point)
    Setting the listbox rowsource to display only the rows which fall between starting point & ending point.

    I will then take the results of the listbox's index value, add it to the value returned for starting point, set that to a variable which controls navigation, and update all fields on the form. (This is meant to provide compiled data from several different worksheets, allowing the user to locate key data.)

    Is there a way to make this code fit into a specified range of rows, while still displaying non-congruent columns?

    Thanks millions for all your help, regulars like yourself help to make the Lounge a wonderful resource! <img src=/S/bow.gif border=0 alt=bow width=15 height=15>
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Listbox Rowsource Property (97 SR2)

    I think I followed that, seems like you just need to replace ws.UsedRange with ws.Rows("" & iStart & ":" & iStop & "") and use the rest of the code intact.

    On second thought, you may also want to create another column and store the worksheet row in it. If you set the width to zero, then the users won't see it. Click on you listbox and press F1 for more details in the help file. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Listbox Rowsource Property (97 SR2)

    Should have said press <F1> to display help and then look at the boundcolumn property.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox Rowsource Property (97 SR2)

    This is working like a charm, Thanks SammyB!

    -Did you know it was possible to make Excel act/behave like Access? Gotta love tight funding... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  10. #10
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Listbox Rowsource Property (97 SR2)

    <img src=/w3timages/blackline.gif width=33% height=2>
    > possible to make Excel act/behave like Access? Gotta love tight funding
    <img src=/w3timages/blackline.gif width=33% height=2>
    and then they will complain about how slow everything works! Someone must be flumoxed! <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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