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 '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''' |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using an input box for multiple choices has display limits
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 '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''' |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using an input box for multiple choices has display limits
Gary Keramidas wrote:
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 I've never used a userform before and have no experience with this area of macros. HOWEVER, this is very interesting. It took me a few sites, and a few moments to get a rough handle on creating and using forms altogether before I was able to get your example to work. After naming the form to frmDropIt, I was able drop in the private codes, and then create the combo. Next, I was finally able to create the module code. Sub test() frmDropIt.Show End Sub I saw from a site that the code may need the .Show at the end of the form name. I assumed this just shows the form and then the form takes it from there. I think that I can expand on this and use it for what I need. I see that I'll need to break out my excel VBA reference books to read up. I never thought that I would ever need a form, but I'm converted. Thanks. |
Reply |
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) |