I want to use a common set of 7 commandbuttons as a 'menu bar' on
several worksheets (each commandbutton opens a worksheet instead of
using the standard tabs)

What is the best way to go about this to minimise code?

Any help much appreciated

Cheers

Peter


--
peter.thompson
------------------------------------------------------------------------
peter.thompson's Profile: http://www.excelforum.com/member.php...o&userid=29686
View this thread: http://www.excelforum.com/showthread...hreadid=503840

Peter,

I add a drop-down to the Formatting toolbar, and in ThisWorkbook, I setup up
the toolbar buton and populate it so

Private Workbook_Open()
With Application.CommandBars("Formatting")
With .Controls.Add(Type:=msoControlDropdown, temporary:=True)
.Caption = "SheetGoto"
.OnAction = "GotoSheet"
End With End With
End Sub

Private Sub Workbook_Activate()
Dim i As Long

With Application.CommandBars("Formatting").Controls("Sh eetGoto")
.Clear
For i = 1 To Wb.Sheets.Count
.AddItem Wb.Sheets(i).Name
Next i
.ListIndex = 1
End With
End Sub

In a standard code module I add this macro to actiavte the sheet

Private Sub GotoSheet()
With Application.CommandBars.ActionControl
ActiveWorkbook.Sheets(.Text).Activate
End With
End Sub

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"peter.thompson"
wrote in
message news:peter.thompson.2220hm_1137963301.313@excelfor um-nospam.com...

I want to use a common set of 7 commandbuttons as a 'menu bar' on
several worksheets (each commandbutton opens a worksheet instead of
using the standard tabs)

What is the best way to go about this to minimise code?

Any help much appreciated

Cheers

Peter


--
peter.thompson
------------------------------------------------------------------------
peter.thompson's Profile:
http://www.excelforum.com/member.php...o&userid=29686
View this thread: http://www.excelforum.com/showthread...hreadid=503840

Use commandbar buttons instead.

otherwise, used the technique shown here at John Walkenbach's site:
http://www.j-walk.com/ss/excel/tips/tip44.htm

--
Regards,
Tom Ogilvy

"peter.thompson"
wrote in
message news:peter.thompson.2220hm_1137963301.313@excelfor um-nospam.com...

I want to use a common set of 7 commandbuttons as a 'menu bar' on
several worksheets (each commandbutton opens a worksheet instead of
using the standard tabs)

What is the best way to go about this to minimise code?

Any help much appreciated

Cheers

Peter


--
peter.thompson
------------------------------------------------------------------------
peter.thompson's Profile:
http://www.excelforum.com/member.php...o&userid=29686
View this thread: http://www.excelforum.com/showthread...hreadid=503840

Thanks guys, much appreciated.

Cheers

Peter


--
peter.thompson
------------------------------------------------------------------------
peter.thompson's Profile: http://www.excelforum.com/member.php...o&userid=29686
View this thread: http://www.excelforum.com/showthread...hreadid=503840