Populating a Listbox, Worksheet Names as Variables

[elcentro3m]

Posted this message

http://www.excelforum.com/showthread.php?t=587338

No replies.

Can anyone here help?

I'm using Excel 2000.

Thanx.

 

[mikerickson]

If the userform is open, you won't be able to open new workbooks, so the list of sheets will be unchanged for the life of the userform
The other post wasn't too clear

are you looking for the loop

for i=0 to workbooks.count
for j=0 to worksheets.count
userform.listbox.additem workbook(i).sheets(j).name
next j:next i

 

[elcentro3m]

More than a few possibilities

Thanks, Mike.

I havent been able to figure out
how to get your code to work.

I did find these examples in
the interim, which I thought
I'd share.

Found this answer to populating a listbox:

Simple and straightforward:
(It assumes a listbox named "Listbox1")

http://groups.google.com/group/microsoft.public.excel.programming/msg/75bb122ae87ed5ea?ic=1

Sub sheetStuff()
    For n = 1 To ActiveWorkbook.Sheets.Count
        UserForm1.ListBox1.AddItem ActiveWorkbook.Sheets(n).Name
    Next
    UserForm1.Show
End Sub

There's also the code from this page:

http://www.exceluciones.com/portal/viewtopic.php?t=229&start=0&postdays=0&postorder=asc&highlight=

modified to:
(which works directly on the UserForm):

Private Sub UserForm_Initialize()

Dim SheetData() As String
Set OriginalSheet = ActiveSheet
ShtCnt = ActiveWorkbook.Sheets.Count
ReDim SheetData(1 To ShtCnt, 1 To 2)
sHTnum = 1

For Each Sht In ActiveWorkbook.Sheets
If Sht.Name = ActiveSheet.Name Then ListPos = sHTnum - 1
SheetData(sHTnum, 1) = Sht.Name
SheetData(sHTnum, 2) = Sht.Range("A1").Value
sHTnum = sHTnum + 1
Next Sht

With ListBox1
.ColumnCount = 2
.ColumnWidths = "150 pt; 50 pt"
.List = SheetData
.ListIndex = ListPos
End With

End Sub

there's this code from:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=130
(which also has an example sheet - zipped)

Private Sub CommandButton1_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
End Sub

Private Sub CommandButton2_Click()
    Unload UserForm1
End Sub

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

Here's another example, from:

http://www.ozgrid.com/forum/showthread.php?t=19248
(This also assumes a listbox named "Listbox1")

Private Sub UserForm_Initialize() 
    Dim SheetData() As String 
    Set OriginalSheet = ActiveSheet 
    ShtCnt = ActiveWorkbook.Sheets.Count 
    ReDim SheetData(1 To ShtCnt, 1 To 4) 
    ShtNum = 1 
    For Each Sht In ActiveWorkbook.Sheets 
        ListPos = ShtNum - 1 
        SheetData(ShtNum, 1) = Sht.Name 
        ShtNum = ShtNum + 1 
    Next Sht 
    With ListBox1 
        .List = SheetData 
    End With 
End Sub

This code moves to the Sheet clicked on:

http://www.developpez.net/forums/showthread.php?p=528782

Option Explicit
Public OriginalSheet As Object
 
Private Sub UserForm_Initialize()
Dim SheetData() As String
Dim ShtNum As Integer
Dim ShtCnt As Integer
Dim Sht As Object
Dim LisPost As Integer
 
Set OriginalSheet = ActiveSheet
ShtCnt = ActiveWorkbook.Sheets.Count
ReDim SheetData(1 To ShtCnt, 1 To 4)
 ShtNum = 1
 For Each Sht In ActiveWorkbook.Sheets
    If Sht.Name = ActiveSheet.Name Then _
    LisPost = ShtNum - 1
SheetData(ShtNum, 1) = Sht.Name
Select Case TypeName(Sht)
    Case "WorkSheet"
    SheetData(ShtNum, 2) = "Feuil"
    SheetData(ShtNum, 3) = Application.CountA(Sht.Cells)
End Select
   ShtNum = ShtNum + 1
    Next Sht
With ListBox1
    .ColumnWidths = "30 pt;30 pt; 40 pt;50 pt"
    .List = SheetData
    .ListIndex = LisPost
End With
End Sub
 
Private Sub ListBox1_Click()
    Sheets(ListBox1.Value).Activate
End Sub

This code provides a list and an InputBox:

http://www.mvdmoosdijk.nl/Excel/ExcelMacros/excelmacros.htm

Sub Go2sheet()
myShts = ActiveWorkbook.Sheets.Count
For i = 1 To myShts
myList = myList & i & " - " & ActiveWorkbook.Sheets(i).Name & " " & vbCr
Next i
Dim mySht As Single
mySht = InputBox("Select sheet to go to." & vbCr & vbCr & myList)
Sheets(mySht).Select
End Sub

and last, there's this (modified) code from
(I forget where):

This requires one listbox, two buttons, and
a label:

Label: "Sheet Name"
Listbox: Listbox1
"Ok" Button: cmdOK
"Cancel" Button: cmdCancel

I call it from a button on a
spreadsheet assigned to
the following macro:

Sub SelectSheet()
    UserForm1.Show
End Sub

Option Explicit

Public OriginalSheet As Object


Private Sub UserForm_Initialize()
    Dim SheetData() As String
    Dim ShtCnt As Integer
    Dim ShtNum As Integer
    Dim Sht As Object
    Dim ListPos As Integer
    
    Set OriginalSheet = ActiveSheet
    ShtCnt = ActiveWorkbook.Sheets.Count
    ReDim SheetData(1 To ShtCnt, 1 To 4)
    ShtNum = 1
    For Each Sht In ActiveWorkbook.Sheets
        If Sht.Name = ActiveSheet.Name Then _
          ListPos = ShtNum - 1
        SheetData(ShtNum, 1) = Sht.Name
        ShtNum = ShtNum + 1
    Next Sht
    With ListBox1
        .ColumnWidths = "60 pt"
        .List = SheetData
        .ListIndex = ListPos
    End With
End Sub

Private Sub cmdCancel_Click()
    OriginalSheet.Activate
    Unload Me
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Call cmdOK_Click
End Sub

Private Sub cmdOK_Click()
    Dim UserSheet As Object
    Set UserSheet = Sheets(ListBox1.Value)
    If UserSheet.Visible Then
        UserSheet.Activate
    Else
        If MsgBox("Unhide sheet?", _
          vbQuestion + vbYesNoCancel) = vbYes Then
            UserSheet.Visible = True
            UserSheet.Activate
        Else
            OriginalSheet.Activate
        End If
    End If
    Unload Me
End Sub

[/code]