Input Box w/Drop Down List
 

I need to have an input box launch that would provide the user with a drop
down box of selections, then upon their selection, would take then to a
certain sheet of a workbook.

I can write the macro - what is baffling me is how to program the drop down
list into the input box instead of having the user type something.

Can this be done?

Input Box w/Drop Down List
 

If you just want something that shows a list of all worksheet names in the
active workbook & from user selection, then selects choosen sheet then
following may do what you want:

Sub SelectSheet()
If ActiveWorkbook.Sheets.Count <= 16 Then
application.CommandBars("Workbook Tabs").ShowPopup 500, 225
Else
application.CommandBars("Workbook Tabs").Controls("More
Sheets...").Execute
End If
End Sub
--
jb


"PMarble" wrote:

I need to have an input box launch that would provide the user with a drop
down box of selections, then upon their selection, would take then to a
certain sheet of a workbook.

I can write the macro - what is baffling me is how to program the drop down
list into the input box instead of having the user type something.

Can this be done?

Input Box w/Drop Down List
 

No, Not with a message box. You can do this using a vba userform.

Add the following to your main sub to show the form.
Public Sub Main()
UserForm1.Show
End Sub

add a combobox and commandbutton to the form with the following code.
Private Sub UserForm_Initialize()
Dim s As Worksheet
Me.ComboBox1.ListRows = Sheets.Count
For Each s In Sheets
Me.ComboBox1.AddItem s.Name, s.Index - 1
Next s
End Sub

Private Sub CommandButton1_Click()
' Call MySub(ComboBox1.Value)
Sheets(ComboBox1.Value).Activate
Unload Me
End Sub


"PMarble" wrote:

I need to have an input box launch that would provide the user with a drop
down box of selections, then upon their selection, would take then to a
certain sheet of a workbook.

I can write the macro - what is baffling me is how to program the drop down
list into the input box instead of having the user type something.

Can this be done?

Input Box w/Drop Down List
 

John, I like your method. I did not know this was available.

"john" wrote:

If you just want something that shows a list of all worksheet names in the
active workbook & from user selection, then selects choosen sheet then
following may do what you want:

Sub SelectSheet()
If ActiveWorkbook.Sheets.Count <= 16 Then
application.CommandBars("Workbook Tabs").ShowPopup 500, 225
Else
application.CommandBars("Workbook Tabs").Controls("More
Sheets...").Execute
End If
End Sub
--
jb


"PMarble" wrote:

I need to have an input box launch that would provide the user with a drop
down box of selections, then upon their selection, would take then to a
certain sheet of a workbook.

I can write the macro - what is baffling me is how to program the drop down
list into the input box instead of having the user type something.

Can this be done?

Input Box w/Drop Down List
 

it's simple & useful although I can't claim originality - it was something I
was shown in days upgrading to 2003.
--
jb


"Kent Prokopy" wrote:

John, I like your method. I did not know this was available.

"john" wrote:

If you just want something that shows a list of all worksheet names in the
active workbook & from user selection, then selects choosen sheet then
following may do what you want:

Sub SelectSheet()
If ActiveWorkbook.Sheets.Count <= 16 Then
application.CommandBars("Workbook Tabs").ShowPopup 500, 225
Else
application.CommandBars("Workbook Tabs").Controls("More
Sheets...").Execute
End If
End Sub
--
jb


"PMarble" wrote:

I need to have an input box launch that would provide the user with a drop
down box of selections, then upon their selection, would take then to a
certain sheet of a workbook.

I can write the macro - what is baffling me is how to program the drop down
list into the input box instead of having the user type something.

Can this be done?

Input Box w/Drop Down List
 

Cool :-)

Neat little trick - thanks for sharing :-)



"john" skrev i en meddelelse
...
If you just want something that shows a list of all worksheet names in the
active workbook & from user selection, then selects choosen sheet then
following may do what you want:

Sub SelectSheet()
If ActiveWorkbook.Sheets.Count <= 16 Then
application.CommandBars("Workbook Tabs").ShowPopup 500, 225
Else
application.CommandBars("Workbook Tabs").Controls("More
Sheets...").Execute
End If
End Sub
--
jb


"PMarble" wrote:

I need to have an input box launch that would provide the user with a
drop
down box of selections, then upon their selection, would take then to a
certain sheet of a workbook.

I can write the macro - what is baffling me is how to program the drop
down
list into the input box instead of having the user type something.

Can this be done?