show sheets into userform...

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.

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

Quote:

---

Originally Posted by dannymking

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...

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

VB Code:

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

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

I'll leave it to the Guru robdog :D :D :D

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

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. ;)

VB Code:

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

This would be the complete code behind the userform.

VB Code:

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

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

VB Code:

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

Quote:

---

Originally Posted by RobDog888

This would be the complete code behind the userform.

VB Code:

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.

Ok, here is a complete working example.

Quote:

---

Originally Posted by RobDog888

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.

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

Quote:

---

Originally Posted by RobDog888

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! :wave: :thumb: