Help with multicolumn listbox

[Peinecone]

I have searched all over, and can't seem to make sense of the multicolumn list box. Basically, what I need to do is populate a list box with 4 columns. I have already set the columncount to 4. What I need to populate the listbox with is
column 1 = the names of all the sheets in the workbook (except the first 2)
column 2 = EachSheet.Cell(4, 6)
column 3 = EachSheet.Cell(6, 6)
column 4 = EachSheet.Cell(7, 9)

Here is what I have to populate the list with the sheet names, but can't figure out the columns.

Private Sub UserForm_Initialize()
Dim sht As Worksheet
Dim i

    lstRecipes.Clear
    On Error Resume Next
    For Each sht In ActiveWorkbook.Sheets
            If sht.Visible = xlSheetVisible Then
               lstRecipes.AddItem "" & sht.Name
            End If
    Next sht
    lstRecipes.RemoveItem (0)
    lstRecipes.RemoveItem (0)

End Sub

Any help is appreciated.

 

[jindon]

try

Private Sub UserForm_Initialize()
Dim i As Long, a(), n As Long
    lstRecipes.Clear
    For i = 3 To ActiveWorkbook.Sheets.Count
            If Sheets(i).Visible = xlSheetVisible Then
               n = n + 1
               ReDim Preserve a(1 To 4, 1 To n)
               With Sheets(i)
                   a(1,n) = .Name
                   a(2,n) = .Cell(4, 6).Text
                   a(3,n) = .Cell(6, 6).Text
                   a(4,n) = .Cell(7, 9).Text
               End With
            End If
    Next sht
    If n > 0 Then lstRecipes.Column = a
End Sub

 

[Peinecone]

I tried your code and am getting an error. First, I did change the last Next statement in your code from "sht" to "i". But it is giving me an error "Object doesn't support this property or method.

 

[Case_Germany]

Hi,

There only one "s" is missing in 3 lines:

a(2, n) = .Cells(4, 6).Text
a(3, n) = .Cells(6, 6).Text
a(4, n) = .Cells(7, 9).Text

Case_Germany

 

[Peinecone]

Perfect, thank you both

 

[Peinecone]

Now to further complicate issues, is it possible to only show certain rows depending on checkboxes. What i mean is, every sheet that populated the list has a category (which is stored in cell "C2"), based on a data validation list. What I would like to be able to do is select checkboxes on the userform, and the sheets relating to that category populate the listbox. Here is the code I am working with

Private Sub UserForm_Initialize()
Dim i As Long, a(), n As Long
    lstRecipes.Clear
    For i = 3 To ActiveWorkbook.Sheets.Count
            If Sheets(i).Visible = xlSheetVisible Then
               n = n + 1
               ReDim Preserve a(1 To 5, 1 To n)
               With Sheets(i)
                   a(1, n) = .Name
                   a(2, n) = .Range("C2").Text
                   a(3, n) = .Range("F26").Text
                   a(4, n) = .Range("F32").Text
                   Select Case Sheets(i).Range("F33").Text
                    Case "#DIV/0!"
                      a(5, n) = ""
                    Case Else
                      a(5, n) = .Range("F33").Text
                   End Select
               End With
            End If
    Next i
    If n > 0 Then lstRecipes.Column = a
End Sub

Then I have 6 checkboxes on the userform named Cat1, Cat2 etc. When I check Cat1, only the sheets with category Cat1 would show in the listbox. When I select Cat1 and Cat2, the sheets with category 1 and the sheets with category 2 would show in the listbox.

 

[Case_Germany]

Hi again,

OK - on the user form are 6 CheckBoxes with names (not Caption) Cat1, Cat2, Cat3… and so on.

Code in "UserForm1":

Option Explicit

Dim objCheckBox() As clsCheckBox

Private Sub UserForm_Activate()
    Dim frmControl As MSForms.Control
    Dim intCount As Integer
    For Each frmControl In Me.Controls
        If TypeOf frmControl Is MSForms.CheckBox Then
            intCount = intCount + 1
            ReDim Preserve objCheckBox(1 To intCount)
            Set objCheckBox(intCount) = New clsCheckBox
            Set objCheckBox(intCount).frmCheckBox = frmControl
        End If
    Next
End Sub

Private Sub UserForm_Terminate()
    Dim intIndex1 As Integer
    For intIndex1 = 1 To UBound(objCheckBox)
        Set objCheckBox(intIndex1) = Nothing
    Next
End Sub

Code in Classmodule named "clsCheckBox":

Option Explicit

Public WithEvents frmCheckBox As MSForms.CheckBox

Private Sub frmCheckBox_Click()
    Dim i As Long, a(), n As Long, b As Long
    With UserForm1
    .lstRecipes.Clear
    For i = 3 To ActiveWorkbook.Sheets.Count
        If Sheets(i).Visible = xlSheetVisible Then
            For b = 1 To 6
            If Sheets(i).Cells(2, 3).Value = .Controls("Cat" & b).Name _
                And .Controls("Cat" & b) = True Then
                n = n + 1
                ReDim Preserve a(1 To 5, 1 To n)
                With Sheets(i)
                    a(1, n) = .Name
                    a(2, n) = .Range("C2").Text
                    a(3, n) = .Range("F26").Text
                    a(4, n) = .Range("F32").Text
                    Select Case Sheets(i).Range("F33").Text
                    Case "#DIV/0!"
                        a(5, n) = ""
                    Case Else
                        a(5, n) = .Range("F33").Text
                    End Select
                End With
            End If
            Next b
        End If
    Next i
    If n > 0 Then .lstRecipes.Column = a
    End With
End Sub

And here an example:

UserForm and Classmodule

Case_Germany

 

[Peinecone]

Thanks man! Worked great.