Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''' |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want excel to input data in a cell from multiple cell choices | Excel Worksheet Functions | |||
Input selection choices | Excel Discussion (Misc queries) | |||
Input box with multiple choices | Excel Programming | |||
Data Table Input Limits | Excel Worksheet Functions | |||
Drop-Down List does not display choices | Excel Discussion (Misc queries) |