Add data in listbox in multiple columns

Question

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

Do the listbox properties reference a cell range in the rowsource property? You could work with that property to change the cell range and add a new item within the new range. — bugdrown
– bugdrown, Commented Feb 9, 2022 at 17:35

CDP1802 · Accepted Answer · 2022-02-09 17:53:56Z

1

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

answered Feb 9, 2022 at 17:53

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kunal Khaire Over a year ago

thank you so much its working and i want to ask 1 more help how can i add headers like the column which shows the sheet name as "Worksheet Names" and for another it will be "Sheet Type" sorry for bothering you but it will help me thank you in advance...

CDP1802 Over a year ago

@kunal unfortunately its not that easy stackoverflow.com/questions/657498/….

user18083442 · Accepted Answer · 2022-02-09 17:15:58Z

0

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

answered Feb 9, 2022 at 17:15

user18083442

562 bronze badges

Collectives™ on Stack Overflow

Add data in listbox in multiple columns

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related