Own Menu-Bar ?

  • Thread starter Thread starter cassy01
  • Start date Start date
C

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
 
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)
 
Back
Top