Listbox vs ListView - Excel 2013

[bs0d]

I've read ListView is not available on 64 bit versions of Excel. I feel like I need ListView for the grid-like features. I also hear Listboxes can be formatted in a similar way? Are there still issues with ListView? Are there any good references for formatting Listboxes like tables? I don't want my data to look like one big blob of words or one big paragraph. Thoughts?

 

[bs0d]

I was going to delete this thread and start a new one that's similar, but instead use this reply as an update.

I need to display a list of data in a container. I was returning the data to the sheet, but was limited by #of rows in the visible area I'm working with. So the data contains two columns: A date, and text. The source of the data is provided via ADO query.

In a very simple way, I need to show a scroll bar if the query results exceed the area of the container. I also need the text of the query (column 2) to wrap if it exceed the container width *but not wrap into the date field, cluttering the appearance.

I'm having trouble either finding the right "container", or formatting the appropriate container as desired. Here's what I've tried:

• ListView - Seems ideal, but not available on all machines? I need compatability across multiple machines.
• ListBox - Can show columns and data fine, displays a scrollbar if data exceeds size, but does not word wrap.
• TextBox - Can achieve a columnar appearance, but no column feature. Displays a scrollbar, but word wrap is a 'full' wrap, and breaks into the dates over the far left, obscuring the data.

Can someone give me some feedback on options to perhaps control the wordwrap of a textbox, or wordwrap a listbox, or try another idea or workaround? Again, it seems the ListView is the ideal object, but I don't want to implement something that may not work for other users.

 

[Kyle123]

There isn't a nice way of doing this. How many records are you talking about?

I reckon the easiest way for complete compatibility if you really need wrapping would be two columns of labels, one for dates and one for the data, though you'd need to work out a way to tell if the text is wrapped and adjust the height accordingly - or just make them all tall since you know the maximum height. Then add a set of labels for each record and bung them in a frame to allow scrolling

 

[bs0d]

Thanks for your reply.

The query returns comments for a selected property (date, and comment). Some comments are long, others short. The older properties have more comments. Comments will only grow through time. But generally, the property with the most comments has about 30. I was displaying on rows up to 25 - and knew I'd face this issue down the road.

The more I look around, the more it seems something so simple isn't as simple as it sounds. But- an alternative I'm toying with is, use a listbox and provide a textbox below. Allow a user click on a row in the listbox and populate the textbox with the full text and scroll enabled.

 

[Kyle123]

That would work, and maybe the best solution. This is the approach I was talking about. Will need a frame adding to a form:

Private Sub UserForm_Initialize()
    
    Dim totalHeight As Long
    Dim rowHeight As Long
    Dim x As Long
    Dim lbl As MSForms.Label
    Const dateLabelWidth As Long = 65
    Const dataLabelWidth As Long = 200
    
    With Me.Frame1
        For x = 0 To 50
            Set lbl = .Controls.Add("Forms.label.1") 'Data
            With lbl
                .Caption = String(x * 10, "x")
                .Top = totalHeight
                rowHeight = autoSizeLabel(lbl)
                .BackColor = &H80000014
                .Left = dateLabelWidth
                .BorderStyle = 1
                .BorderColor = &H8000000F
                .Width = dataLabelWidth
                                rowHeight = autoSizeLabel(lbl)
            End With
            With .Controls.Add("Forms.Label.1") 'Date
                .Width = dateLabelWidth
                .Caption = "12 Apr 2016"
                .Top = totalHeight
                .Height = rowHeight
                .BackColor = &H80000014
                .Left = 0
                .BorderStyle = 1
                .BorderColor = &H8000000F
            End With
            
            totalHeight = totalHeight + rowHeight
            
        Next x
        .BackColor = &H80000014
        .ScrollBars = fmScrollBarsVertical
        .ScrollHeight = totalHeight
    End With
    
End Sub

Private Function autoSizeLabel(ByVal lbl As MSForms.Label) As Long
    lbl.AutoSize = False
    lbl.AutoSize = True
    
    autoSizeLabel = lbl.Height
    
End Function

 

[bs0d]

Ok, interesting. I'll have to try that out to see how it looks.

 

[Kyle123]

This is a bit more versatile:

Option Explicit

Private Sub UserForm_Initialize()
    
    Dim totalHeight As Long
    Dim rowHeight As Double
    Dim lbl As MSForms.Label
    Dim x As Long
    Const dateLabelWidth As Long = 65
    Dim dataLabelWidth As Double
    dataLabelWidth = (Me.Frame1.Width - dateLabelWidth) - 16 'Full width less scrollbar
    
    With Me.Frame1
        For x = 0 To 100
            Set lbl = .Controls.Add("Forms.label.1") 'Data
            With lbl
                .Caption = String(x * 10, "x")
                .Top = totalHeight
                .BackColor = &H80000014
                .Left = dateLabelWidth
                .BorderStyle = 1
                .BorderColor = &H8000000F
                .Width = dataLabelWidth
                rowHeight = autoSizeLabel(lbl)
                If lbl.Width < dataLabelWidth Then lbl.Width = dataLabelWidth
            End With
            With .Controls.Add("Forms.Label.1") 'Date
                .Width = dateLabelWidth
                .Caption = "12 Apr 2016"
                .Top = totalHeight
                .Height = rowHeight
                .BackColor = &H80000014
                .Left = 0
                .BorderStyle = 1
                .BorderColor = &H8000000F
            End With
            
            totalHeight = totalHeight + rowHeight
            
        Next x
        .BackColor = &H80000014
        .ScrollBars = fmScrollBarsVertical
        .ScrollHeight = totalHeight
    End With
    
End Sub

Private Function autoSizeLabel(ByVal lbl As MSForms.Label) As Double
    lbl.AutoSize = False
    lbl.AutoSize = True
    lbl.Height = lbl.Height + 10
    autoSizeLabel = lbl.Height
    
End Function

 

[bs0d]

Excellent, thanks