[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

Create VBA Listbox: 3 columns - Worksheet Index, Name and Visble/Hidden property for all sheets in workbook

i want to create a simple Listbox userform that shows all the worksheets in the active workbook with the worksheet number / name / whether or not visible

So far I have: (with a listbox called Lbx_Worksheets)

Private Sub UserForm_Initialize()
    Dim i As Long
    For i = 1 To ActiveWorkbook.Worksheets.Count
    With Lbx_Worksheets
        .AddItem i
        .AddItem Worksheets(i).Name
        
    End With
    Next i
    
End Sub

Open in new window


but this justs lists the items in one column.

Can anyone assist, please?
What is the syntax for  showing if the worksheet property is hidden or visible?
I have set the columncount property to 3

Thank you
0
David Phelops
Asked:
David Phelops
  • 2
1 Solution
 
Robert SchuttSoftware EngineerCommented:
Can you try this:
Private Sub UserForm_Initialize()
    Dim i As Long
    For i = 1 To ActiveWorkbook.Worksheets.Count
    With Lbx_Worksheets
        .AddItem i
        .List(i - 1, 1) = Worksheets(i).Name
        .List(i - 1, 2) = IIf(Worksheets(i).Visible, "visible", "hidden")
    End With
    Next i
End Sub

Open in new window

0
 
Rory ArchibaldCommented:
Not for points:

Note that you need to change the listbox columncount to 3 if you haven't already. ;)
0
 
David PhelopsAuthor Commented:
Thanks very much; works lovely!
You have introduced me to "IIF" as well, so thank you for that.

I have experimented and ended up with a nice info box:

Cheers
David
0
 
David PhelopsAuthor Commented:
Thanks Rorya - I added the columncount as a prpoperty, then changed it to be set in the code.
Cheers
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now