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?

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?

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?

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?

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?

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?