Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Jul 2008
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Displaying all cells in a listbox (2000)

    I have the following code to display a range of cells in a listbox. Does anyone know how I can display all the rows instead of a range. I cant enter A65536 because it shows me all the blank rows too.

    Private Sub CommandButton2_Click()
    Dim lbtarget As MSForms.ListBox
    Dim rngSource As Range

    'Set reference to the range of data to be filled
    Set rngSource = Worksheets("Residents").Range("A2:H700")

    'Fill the listbox
    Set lbtarget = Me.ListBox1
    With lbtarget
    'Determine number of columns
    .ColumnCount = 8
    'Set column widths
    .ColumnWidths = "50;80;100"
    'Insert the range of data supplied
    .List = rngSource.Cells.Value
    End With

    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Displaying all cells in a listbox (2000)

    How about something like this:

    with Worksheets("Residents")
    Set rngSource = .range(.range("H2"), .cells(.rows.count,1).end(xlup))
    end with

    It will use H2 and the last occupied cell in col A...

    Steve

  3. #3
    New Lounger
    Join Date
    Jul 2008
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying all cells in a listbox (2000)

    just what i wanted. thank you very much!

  4. #4
    New Lounger
    Join Date
    Jul 2008
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying all cells in a listbox (2000)

    how can i add to this code to set the column heads and change their background colour?

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Displaying all cells in a listbox (2000)

    You can only use the Column Headers if you set the rowsource of the listbox to a range address. There is no way to colour them that I know of (possibly you could do something with the Windows API):
    <pre>Dim rngSource As Range

    'Set reference to the range of data to be filled
    With Worksheets("Residents")
    Set rngSource = .Range(.Range("H2"), .Cells(.Rows.Count, 1).End(xlUp))
    End With
    rngSource.Name = "ListData"
    'Fill the listbox
    With Me.ListBox1
    'Determine number of columns
    .ColumnCount = 8
    .ColumnHeads = True
    'Set column widths
    .ColumnWidths = "50;80;100"
    'Insert the range of data supplied
    .RowSource = "ListData"
    End With
    </pre>

    for example.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Displaying all cells in a listbox (2000)

    You can only set column headings for a list box by setting the ColumnHeads property to True and setting RowSource property of the list box to the address of a range. The row *above* the RowSource will automatically be used as column headings.

    It won't work if you populate the list box otherwise, for example by setting its List property or by using the AddItem method.

    You could use code like this:
    <code>
    Dim m As Long
    With Worksheets("Residents")
    m = .Cells(.Rows.Count,1).End(xlUp).Row
    End With
    ...
    With lbTarget
    ...
    .ColumnHeads = True
    .RowSource = "Residents!A2:H" & m
    End With
    </code>
    It is not possible to change the formatting of the column headings, at least not easily (you'd need to use Windows API functions).

  7. #7
    New Lounger
    Join Date
    Jul 2008
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying all cells in a listbox (2000)

    Thank you both for your help. Colour would have been nice, but shall leave it as it is i think. thanks again

  8. #8
    New Lounger
    Join Date
    Jul 2008
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying all cells in a listbox (2000)

    im having a problem with an error im getting. i start off with the list box not visible. When I click my command button it displays all the cells correctly in the list box and makes it visible. I then hide this button and display another command button. When this is clicked it hides the listbox and itslelf and re-displays the original button. All this works fine, but when i click the button to display the list box again I have an error "permission denied". Anyone know why this is happening. the code i have is below,.

    Private Sub cmdShowResidents_Click()
    Dim lbtarget As MSForms.ListBox
    Dim rngSource As Range
    Dim m As Long

    ListBox1.Visible = True
    'Set reference to the range of data to be filled
    With Worksheets("Residents")
    Set rngSource = .Range(.Range("G2"), .Cells(.Rows.Count, 1).End(xlUp))
    End With

    'Fill the listbox
    Set lbtarget = Me.ListBox1
    With lbtarget
    'Determine number of columns
    .ColumnCount = 8
    'Set column widths
    .ColumnWidths = "80;80;80;80;80;140;80"
    'Insert the range of data supplied
    .List = rngSource.Cells.Value
    End With

    With Worksheets("Residents")
    m = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With

    With lbtarget

    .ColumnHeads = True
    .RowSource = "Residents!A2:G" & m
    End With

    cmdShowResidents.Visible = False
    cmdHideAllOfficers.Visible = True
    End Sub

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Displaying all cells in a listbox (2000)

    Which line is highlighted when you get the error message and click Debug?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Displaying all cells in a listbox (2000)

    Oh wait, you should have removed the code that sets the List property. It isn't needed any more if you set the RowSource property and as you have found it causes a conflict. You can simplify your code to
    <code>
    Private Sub cmdShowResidents_Click()
    Dim m As Long

    With Worksheets("Residents")
    m = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With

    ' Fill the listbox
    With Me.ListBox1
    ' Determine number of columns
    .ColumnCount = 8
    ' Set column widths
    .ColumnWidths = "80;80;80;80;80;140;80"
    ' Specify column headings
    .ColumnHeads = True
    ' Insert the range of data supplied
    .RowSource = "Residents!A2:G" & m
    ' Show list box
    .Visible = True
    End With

    Me.cmdShowResidents.Visible = False
    Me.cmdHideAllOfficers.Visible = True
    End Sub
    </code>
    BTW The ColumnCount, ColumnWidths and ColumnHeads properties appear to be fixed, so you can set them in the Properties pane in the Visual Basic Editor. You don't have to set them in code, only the RowSource (since m can vary).

Posting Permissions

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