PDA

View Full Version : Solved: Loading a listbox with sheet names



xltrader100
09-17-2008, 05:02 PM
I'm using the following to load a listbox with the names of all the hidden sheets.

ListBox1.CLEAR
With ActiveWorkbook
For i = 1 To .Sheets.Count
If .Sheets(i).Visible = False Then
ListBox1.AddItem (.Sheets(i).Name)
End If
Next
End With

This is working fine except it's giving me the sheet Code names and I want the Tab names. How to convert the Code names to Tab names

lucas
09-17-2008, 05:23 PM
Try this:

Private Sub UserForm_Initialize()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ListBox1.AddItem (ws.Name)
Next ws
End Sub

lucas
09-17-2008, 05:25 PM
To activate a sheet using the listbox:
Private Sub ListBox1_Click()
Dim i As Integer, sht As String
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
sht = ListBox1.List(i)
End If
Next i
Sheets(sht).Activate

End Sub

lucas
09-17-2008, 05:35 PM
Actually, after I looked at your code I realized you just want to show the hidden sheets by name and your code seems to work....try the attachment to this post.

The hitten sheets tab name is 555

xltrader100
09-17-2008, 05:54 PM
Thanks, lucas, works great and much cleaner to boot.