PDA

View Full Version : Solved: Form to change sheets



Djblois
01-03-2007, 03:06 PM
I want to create a macro that will read all the tabs in the active workbook and populate a form with the names to make it easier to switch between them (for books with too many tabs). I know how to create the form but I need help at least starting the code. I don't know how to populate the names onto the form.

lucas
01-03-2007, 03:12 PM
uses a listbox on a userform:

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
Private Sub UserForm_Initialize()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ListBox1.AddItem (ws.Name)
Next ws
End Sub

Djblois
01-08-2007, 01:39 PM
Lucas,

Sorry to bother you but I just tried this code and there are 2 problems
1) It doesn't list the sheets (by name)
2) It crashes when I click on the list box

lucas
01-08-2007, 03:02 PM
Crash is brash....what does it mean in this instance....
works for me....what are you doing with the code Daniel?
you have to create a userform and put a listbox on it...listbox1
put the code in the userform code module

Djblois
01-08-2007, 03:03 PM
I created a userform and put the code in the userform module. Sorry, let me try a few things

lucas
01-08-2007, 03:15 PM
I just inserted a userform...added the listbox and pasted the code from the previous post.....see attached. Let me know what you think the problem might have been.

Djblois
01-09-2007, 10:31 AM
Lucas,

It is weird I have the same exact code as you but it doesn't work. The only thing I was missing is the option explicit which I added and it still doesn't work.

Daniel

Djblois
01-09-2007, 10:40 AM
I figured it out:

I had to change ThisWorkBook to ActiveWorkBook

lucas
01-09-2007, 03:50 PM
You must be using it in an addin.....that would make that specific difference....