I am definitely new to this VBA world. I am trying to make a userform
listing all the sheets in a very large workbook so I can select on the name
listed. The result would be once the selection is made it would take me to
that sheet. This is the code I have so far....


Private Sub ListBox1_Click()
Sheet.Select
End Sub

Private Sub UserForm_Initialize()
For i = 1 To Sheets.Count
ListBox1.AddItem Sheets(i).Name
Next i
End Sub


What am I doing wrong?


Thanks Ted

Ted,
You didn't provide enough information to Excel to be able to identify the sheet.
Also, it is good practice to use "Option Explicit" as the first line in every module.
It will force you to declare variables such as "i"
'------------------
'Hope you have a "Close" button on the form?
Private Sub CommandButton1_Click()
Me.Hide
Unload Me
End Sub

Private Sub ListBox1_Click()
Worksheets(ListBox1.Value).Select
End Sub

Private Sub UserForm_Initialize()
Dim i As Long
For i = 1 To Sheets.Count
ListBox1.AddItem Sheets(i).Name
Next 'i
End Sub
'----------------------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"tac"
wrote in message...
I am definitely new to this VBA world. I am trying to make a userform
listing all the sheets in a very large workbook so I can select on the name
listed. The result would be once the selection is made it would take me to
that sheet. This is the code I have so far....

Private Sub ListBox1_Click()
Sheet.Select
End Sub

Private Sub UserForm_Initialize()
For i = 1 To Sheets.Count
ListBox1.AddItem Sheets(i).Name
Next i
End Sub

What am I doing wrong?
Thanks Ted

Jim:

Thanks for the help. Your suggestions for "good form" are greatly
appreciated.

Thanks Again

Ted

"Jim Cone" wrote:

Ted,
You didn't provide enough information to Excel to be able to identify the sheet.
Also, it is good practice to use "Option Explicit" as the first line in every module.
It will force you to declare variables such as "i"
'------------------
'Hope you have a "Close" button on the form?
Private Sub CommandButton1_Click()
Me.Hide
Unload Me
End Sub

Private Sub ListBox1_Click()
Worksheets(ListBox1.Value).Select
End Sub

Private Sub UserForm_Initialize()
Dim i As Long
For i = 1 To Sheets.Count
ListBox1.AddItem Sheets(i).Name
Next 'i
End Sub
'----------------------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"tac"
wrote in message...
I am definitely new to this VBA world. I am trying to make a userform
listing all the sheets in a very large workbook so I can select on the name
listed. The result would be once the selection is made it would take me to
that sheet. This is the code I have so far....

Private Sub ListBox1_Click()
Sheet.Select
End Sub

Private Sub UserForm_Initialize()
For i = 1 To Sheets.Count
ListBox1.AddItem Sheets(i).Name
Next i
End Sub

What am I doing wrong?
Thanks Ted