Mac Forum / Applications / Excel / May 2008

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