Using an input box for multiple choices has display limits

[jbarrington]

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

 

[Gary Keramidas]

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

 

[jbarrington]

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.