View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
jbarrington jbarrington is offline
external usenet poster
 
Posts: 8
Default Using an input box for multiple choices has display limits

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