show sheets into userform...

[luca90]

Assuming i have many sheet into a wbook, named GENNAIO_2004, FEBBRAIO_2005, MARZO_2003 etcc....
Is possible to show into a userform the list of all sheet and when i click on one of this, select and show the selected sheet?

note:
into wbook i have also a shett named "controllo" and "abicab" in effect for me is important only to show the sheets na,med with MONTH and YEAR.

[dannymking]

How are you displaying these on the userform, in a listbox?

[luca90]

How are you displaying these on the userform, in a listbox?

I am sorry into userform with the tipical drag bar with arrow up/down...

[RobDog888]

You can do something like this to generate a string listing of all sheets.

Public Function ListSheets() As String
    Dim sSheets As String
    Dim i As Integer
    For i = 1 To ActiveWorkbook.Sheets.Count
        sSheets = sSheets & ActiveWorkbook.Sheets(i).Name & ";"
    Next
    ListSheets = Left$(sSheets, Len(sSheets) - 1)
End Function

[dannymking]

I've just finished work so I'm not gonna have chance to explain..

I'll leave it to the Guru robdog

But you would be better of putting these into a listbox..

[RobDog888]

Then you can load your userform with this code behind it. The UF contains a combobox control which is probably best to use in this situation.

Private Sub UserForm_Initialize()
    Dim ar() As String
    Dim i As Integer
    
    ar = Split(ThisWorkbook.ListSheets, ";")
    For i = 0 To UBound(ar)
        ComboBox1.AddItem ar(i)
    Next
    Erase ar
End Sub

[RobDog888]

This would be the complete code behind the userform.

Option Explicit
 
Private Sub ComboBox1_Click()
    ActiveWorkbook.Sheets(ComboBox1.ListIndex + 1).Activate
End Sub
 
Private Sub UserForm_Initialize()
    Dim ar() As String
    Dim i As Integer
    
    ar = Split(ThisWorkbook.ListSheets, ";")
    For i = 0 To UBound(ar)
        ComboBox1.AddItem ar(i)
    Next
    Erase ar
End Sub

[RobDog888]

Also add this code behind the userform for when the user types in the sheet name to switch to.

Private Sub ComboBox1_Change()
    ActiveWorkbook.Sheets(ComboBox1.ListIndex + 1).Activate
End Sub

[luca90]

This would be the complete code behind the userform.

Option Explicit
 
Private Sub ComboBox1_Click()
    ActiveWorkbook.Sheets(ComboBox1.ListIndex + 1).Activate
End Sub
 
Private Sub UserForm_Initialize()
    Dim ar() As String
    Dim i As Integer
    
    ar = Split(ThisWorkbook.ListSheets, ";")
    For i = 0 To UBound(ar)
        ComboBox1.AddItem ar(i)
    Next
    Erase ar
End Sub

tks RobDog888 but i am a new bie not is possible to attach an example?
Tks and pizza for you.
Sal from Napoli.

[RobDog888]

Ok, here is a complete working example.

[luca90]

Ok, here is a complete working example.

Hi Rob, tks for code... work fine.
But have an idea for this:
http://www.vbforums.com/showthread.php?t=351557
Very important for me.
Tks 2 pizza from Napoli.

[RobDog888]

Havent done much with automating IE but I will take a deeper look later. I need to take care of something.

[luca90]

Havent done much with automating IE but I will take a deeper look later. I need to take care of something.

4 pizzas for you and 2 coffe from Napoli!