I already see a bug that the code blow when you select a sheet that is not in the workbook
before you refresh the sheet list.

Will correct that after your comments



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message ...
Hi Ray

I attached a 2007 test add-in because it is not so easy to post the
VBA and the XML in this thread.

It will create a group on the right side of the home tab with the Nav options

Test it for me because I not spend much time to create it.
When it is OK I clean it up and we can put it online on Debra's site or mine.

I don't really use the 'Styles' section too much
It is possible to move this if you want

If your newsreader kill the attachment mail me private



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ray" wrote in message ...
Hi Ron -

I've got an add-in (built for XL2003) that has been extremely useful
to me and now that my company has migrated to XL2007, I find that I
really miss it .... this code was originally built by Dave Peterson,
but he referred me to you (due to your XL2007 knowledge). I hope you
can help ...

The code basically builds a small drop-down 'menu' by which I'm able
to easily navigate through all of the sheets in the active workbook
(using 'refresh' to update for a new book). I should note that the
code DOES WORK in XL2007 ... however, instead of using a drop-down
that's anchored to the bottom of my window, I have to click on Add-Ins
(in the Ribbon) first. It's just one extra step, but over the past 2
weeks, I've found it to be tiresome.

So, here's my question - can this code be modified to do the same
thing (anchored to bottom of window) in XL2007 and if so, how? I know
I can use the UI Editor to change the Ribbon itself, but I don't have
time to figure this part out.

Thanks in Advance,
Ray


Here's the code:
Sub Auto_Open()
'code written by Dave Peterson 2005-12-21
'creates a toolbar with list of sheets in active workbook

Dim cb As CommandBar
Dim ctrl As CommandBarControl

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

Set cb = Application.CommandBars.Add(Name:="myNavigator",
Position:=msoBarBottom, 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
.Width = 300
.AddItem "Click Refresh First"
.OnAction = ThisWorkbook.Name & "!changethesheet"
.Tag = "__wksnames__"
End With
End With

End Sub