Add data in listbox in multiple columns-CodePudding

recently i came across a userfrom which have list box which shows the sheets name in first column and there property like are they visible or hidden in second columns. but when i tried to access the codes its lock i googled and did every thing to understand how it is done but i didnt found it hope i get my answer here below is mycode which i did i am noob please help me

my code:-

Private Sub UserForm_Initialize()

Dim ws As Worksheet


ListBox1.ColumnCount = 2
For Each ws In ThisWorkbook.Worksheets

    Me.ListBox1.AddItem ws.Name

Next ws

End Sub

CodePudding user response：

Private Sub UserForm_Initialize()

    Dim ws As Worksheet, n As Long, arText
    arText = Array("xlSheetVisible", "xlSheetHidden", "", "xlSheetVeryHidden")
    
    ListBox1.ColumnCount = 2
    For Each ws In ThisWorkbook.Worksheets
        Me.ListBox1.AddItem ws.Name
        Me.ListBox1.List(n, 1) = arText(ws.Visible   1)
        n = n   1
    Next ws

End Sub

CodePudding user response：

There are several possibilities. One of them is:

Sub ListBoxMitArrayAusTabelleFuellen()
 Dim VarDat As Variant
 Dim lngRowMax As Long

 With Tabelle1

  lngRowMax = .Range("A" & .Rows.Count).End(xlUp).Row
  VarDat = .Range("A2:B" & lngRowMax ).Value

  .ListBox1.ColumnCount = UBound(VarDat, 2)
  .ListBox1.Clear
  .ListBox1.List = VarDat

 End With

End Sub

BG Bernd