you may be able to use a combobox.

create a new test userform and just add 1 combobox to it, then paste this
code and see if it will give you some ideas.

Private Sub ComboBox1_Change()
Worksheets(Me.ComboBox1.Value).Activate
End Sub

Private Sub UserForm_Activate()
Dim i As Long
For i = 1 To Worksheets.Count
Me.ComboBox1.AddItem Worksheets(i).Name
Next
End Sub


--


Gary

"jbarrington" <"jbarrington -at- comcast period net" wrote in message
. ..
I have a macro that lets the user choose the workbook file among other
things, but I'm also trying to create a point where the user can select
the worksheet from a list from that workbook.

My problem is the input box only seems to allow a maximum of 5 lines,
I know that I will have instances where there are more than 5
worksheets within that workbook file.

Is there anyway that I can over come this 5 line limit, or can I
substitute a drop down box that is tied to the macro subroutine?
I've used drop down boxes within excel, but I've never used one
for a macro. Of course, I guess there could be another multiple
choice option that allows the user to choose from a list that I
can't think of.

Here's the code that I have now:
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''
Sub ChooseSheet()
'This routine will provide a message box
'with a list of all the names within a workbook

Dim wksht As Worksheet
Dim i As Long
Dim wkshtnames() 'This is an array definition
Dim TheList As String

i = 0

For Each wksht In ActiveWorkbook.Worksheets
i = i + 1
ReDim Preserve wkshtnames(1 To i)
wkshtnames(i) = i & ". " & wksht.Name
Next wksht

TheList = "0. Exit without choosing." & vbCrLf

For i = LBound(wkshtnames) To UBound(wkshtnames)
TheList = TheList + wkshtnames(i) & vbCrLf
Next i

'This will only display five choices. If the workbook has
'a higher number of worksheets than that, this will result
'in those numbers not showing up.

Dim lNum As Long

On Error Resume Next
Application.DisplayAlerts = False
lNum = Application.InputBox _
(Prompt:=TheList, _
Title:="Choose a Worksheet Number", Type:=1)
On Error GoTo 0
Application.DisplayAlerts = True

If lNum = 0 Then
Exit Sub
Else
'Do something here
End If

End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''