Deb said:
This is a newsgroup for Mac XL (hence the .mac. in the title), but this
is one way you can do it in any version other than MacXL08:
Put this in the worksheet code module (right-click the worksheet tab and
choose View Code):
Private Sub Worksheet_Activate()
'Create a new bar, or make changes...
'For instance:
On Error GoTo ResetMenu
With Application.CommandBars(1)
With .Controls.Add( _
Type:=msoControlPopup, Temporary:=True)
.Caption = "My Menu"
.Tag = "MyMenu"
With .Controls.Add( _
Type:=msoControlButton, Temporary:=True)
.Caption = "My Control"
.Tag = "MyControl"
.OnAction = "MyMacro"
.Style = msoButtonCaption
.TooltipText = "run my macro"
.Visible = True
.Enabled = True
End With
.Visible = True
.Enabled = True
End With
End With
ExitActivate:
Exit Sub
ResetMenu:
Application.CommandBars(1).Reset
Resume ExitActivate
End Sub
Private Sub Worksheet_Deactivate()
On Error GoTo EndDeactivate
'Either restore the original bar,
'undo the individual changes (preferred), e.g.:
Application.CommandBars.FindControl(Tag:="MyMenu").Delete
'or
'Application.CommandBars(1).Reset
ExitDeactivate:
Exit Sub
EndDeactivate:
'handle error here - or reset menu bar
Resume ExitDeactivate
End Sub
