Own Menu-Bar ?

[cassy01]

is it possible to make your own menu-bar accross the top of th
worksheet, getting rid of the one thats there and making your own ????

so in other words i open this file up on any computer and it shows m
customized menu and when i open up another worksheet it will go back t
normal.

so the menu bar will only work on that one workbook ??

thank

 

[Bob Phillips]

You could hide all the existing controls and add your own, like so
Option Explicit

Dim ary
Dim cAry As Long

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCB As CommandBar
Dim oCtl As CommandBarButton
Dim i As Long

Set oCB = Application.CommandBars(1)
For i = 1 To UBound(ary)
oCB.Controls(ary(i)).Visible = True
Next i
oCB.Controls("Test").Delete
End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
Dim oCtl As CommandBarControl


Set oCB = Application.CommandBars(1)
ReDim ary(1 To 1)
cAry = 1
For Each oCtl In oCB.Controls
oCtl.Visible = False
ReDim Preserve ary(1 To cAry)
ary(cAry) = oCtl.Caption
cAry = cAry + 1
Next oCtl
Set oCtl = oCB.Controls.Add(Type:=msoControlButton, temporary:=True)
With oCtl
.Caption = "Test"
.Style = msoButtonCaption
.OnAction = "myMacro"
End With

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)