CommandButton code help

[peter.thompson]

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

 

[Bob Phillips]

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("SheetGoto")
.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"

 

[Tom Ogilvy]

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

 

[peter.thompson]

Thanks guys, much appreciated.

Cheers

Peter