Using Promp in input box to move between sheets in a workbook

[Brian Milliner]

I am looking for a macro that I can use to move between the sheets in a
workbook by using promp in an "input box". I have a series of sheets with
tables and information on all of them. I want to be able to press a button
which will activate a macro with a prompt, eg : "To which sheet do you want
to go?" Then I want to see a list of the sheets as one has when one "right
clicks" on the arrows on the bottom lefthand corner of the the worksheet and
click on the name of the sheet to which I want to go. I should be able to
add a sheet to the workbook and it will automatically be added to the list.

I hope that that is not too tall an order.

Thank you

Brian

[masim]

Please find attached some working on this.

This is a bit raw but could be helpful.

Regards
Asim

File Attached: http://www.exceltip.com/forum/attachment.php?postid=260847 (sheetstest.xls)

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

[Dave Peterson]

How about something like this:

Option Explicit
Sub auto_close()
On Error Resume Next
Application.CommandBars("MyNavigator").Delete
On Error GoTo 0
End Sub

Sub auto_open()

Dim cb As CommandBar
Dim ctrl As CommandBarControl
Dim wks As Worksheet

On Error Resume Next
Application.CommandBars("MyNavigator").Delete
On Error GoTo 0

Set cb = Application.CommandBars.Add(Name:="myNavigator", temporary:=True)
With cb
.Visible = True
Set ctrl = .Controls.Add(Type:=msoControlButton, temporary:=True)
With ctrl
.Style = msoButtonCaption
.Caption = "Refresh Worksheet List"
.OnAction = ThisWorkbook.Name & "!refreshthesheets"
End With

Set ctrl = .Controls.Add(Type:=msoControlComboBox, temporary:=True)
With ctrl
.AddItem "Click Refresh First"
.OnAction = ThisWorkbook.Name & "!changethesheet"
.Tag = "__wksnames__"
End With
End With

End Sub
Sub ChangeTheSheet()

Dim myWksName As String
Dim wks As Worksheet

With Application.CommandBars.ActionControl
myWksName = .List(.ListIndex)
End With

Set wks = Nothing
On Error Resume Next
Set wks = Worksheets(myWksName)
On Error GoTo 0

If wks Is Nothing Then
Call refreshthesheets
MsgBox "Please try again"
Else
wks.Select
End If

End Sub
Sub refreshthesheets()
Dim ctrl As CommandBarControl
Dim wks As Worksheet

Set ctrl = Application.CommandBars("myNavigator") _
.FindControl(Tag:="__wksnames__")
ctrl.Clear

For Each wks In ActiveWorkbook.Worksheets
ctrl.AddItem wks.Name
Next wks
End Sub

If you add a worksheet or change workbooks, just click on the "refresh" button,
then use the dropdown.

--

Dave Peterson
ec3...@msn.com