Adding Menu Items????

[ribhead]

I am trying to create a menu item with a macro assigned to it that will only be used for one worksheet so everytime the user opens this up it will be created and when he/she exits I will reset the menubars using MenuBars(xlworksheet).Reset This is probably easy to do but all I have is a manual from 1992 and I'm not comprehending what the manual is saying. Below is what was recorded.

Application.CommandBars("Worksheet Menu Bar&quot.Controls.Add Type:= _
msoControlPopup, Before:=10

I may not be very smart but I'm sure wirey!!!!

 

[Stretchwickster]

Here is an example for you to play with:

  'Create command bar
    Set tsBar = CommandBars.Add(Name:="Timesheet")
    
  'Set command bar properties
    With tsBar
      .Position = msoBarFloating
      .Top = 130
      .Left = 165
      .Visible = True
    End With
    
   'Add controls  
   'Specific Macro button
    Set SpecificMacroBtn = tsBar.Controls.Add(Type:=msoControlButton)
    With SpecificMacroBtn
      .Style = msoButtonCaption
      .Caption = "Specific macro..."
      .OnAction = "Run_Specific_Macro"
    End With

The important line is the .OnAction line which specifies which macro to run. Run_Specific_Macro is a routine defined in one of my modules.



Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[ribhead]

I'm getting confused here sorry, I typed this in and it does work however all I want to do is to place the menu item to the left of "Help". I also want to delete this out
during Auto_Close.

sub Autpen
'This is where I want to create a new menu item with a Macro
assigned to it.

sub Auto_Close
'This is where I want to get rid of the new menu item so it's not visible on any other Excel sheet.

MenuBars(xlworksheet).Reset

I do appreciate the help. No Really I'm just very very green.



I may not be very smart but I'm sure wirey!!!!

 

[ribhead]

I used pretty much what you gave me, I did modidfy it a bit though Thanks a bunch.
Autpen
'Create command bar
Set tsBar = CommandBars.Add(Name:="MainMenuBar&quot

'Set command bar properties
With tsBar
.Position = msoBarTop

.Visible = True
End With

'Add controls
'Specific Macro button
Set SpecificMacroBtn = tsBar.Controls.Add(Type:=msoControlButton)
With SpecificMacroBtn
.Style = msoButtonCaption
.Caption = "&MainMenu"
.OnAction = "mainmenu"
End With. Thanks a bunch.

Auto_Close
Toolbars("MainMenuBar&quot.Delete

I may not be very smart but I'm sure wirey!!!!