Populating second column of list box 1

[bluegnu]

Hello,

I have a form with a listbox which is populated with the names of sheets in my workbook. I want to have 2 columns, the first is the sheet name and the second will be the value of a cell on that sheet. This is the code I currently have:

Private Sub UserForm_Initialize()
    Dim wbs As Worksheet
    
    For Each wbs In Worksheets
        If wbs.Name <> "Contents" Then
        If wbs.Name <> "Status" Then
        If wbs.Name <> "Introduction" Then
        If wbs.Name <> "Template" Then
        If wbs.Range("G10") > 0 Then
            ListBox1.ColumnCount = 2
            ListBox1.AddItem wbs.Name, 0
            ListBox1.AddItem wbs.Range("D4"), 1
            
        End If
        End If
        End If
        End If
        End If
    Next

End Sub

This doesn't work because the sheet name and the cell contents are showing on different lines on the list in teh first column. Is it possible for me to get the sheet name in the first and the cell contents in the second column?

thanks

 

[PHV]

You may try this:
...
ListBox1.AddItem wbs.Name
ListBox1.Column 1, ListBox1.ListCount - 1 = wbs.Range("D4")
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bluegnu]

Thanks, I have tried this and I get a "Compile Error: Invalid Use of property" and the .Range is highlighted.

Any ideas?

 

[PHV]

Sorry for the typo:
ListBox1.Column(1, ListBox1.ListCount - 1) = wbs.Range("D4").Value

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bluegnu]

Excellent thanks

 

[Aaron5]

PHV

I'm having a similar problem trying to use a vba list box they way its used in vb6(example I have) to post random lines of text from code to indicate the status of a small running serial comm program.

I just want to add one line at a time. The vb6 version was using:
frmName.List4.AddItem "Some text here"

I can list line items in vba using:
Me!.List4.AddItem "Text message"

-But it wraps 1/2 way across the width of my list box.?
-How can I Clear the list box with a code?, The .Clear is not available in vba.

Thanks