Adding Menu Items to excel 2007

[Jeff]

I had this working fine in 2003, when I open the worklog file I have,
it would create an additional menu item that had more selections under
it, but in 2007 it doesnt seem to work.

I have the security set to allow macros

Here is the code I have been using, can anyone make a suggustion that
might help me get this going in Excel 2007?

Thanks,

Jeff


Private Sub auto1()
With ActiveWorkbook
.RunAutoMacros xlAutoOpen
.Open
End With
End Sub

'CREATE THE MENU
Sub auto_open()
Dim CncMenu As Object
Dim ThisSubMenu As Object
Dim ThisMenuItem As Object
Set CncMenu = CommandBars.FindControl(Tag:="CNC-Menu")
If CncMenu Is Nothing Then
Set CncMenu = CommandBars("Worksheet Menu
Bar").Controls.Add(Type:=msoControlPopup)
With CncMenu
.Caption = "WORK LOG"
.Tag = "CNC-Menu"
'update hyperlink file dates
'highlite rows
Set ThisMenuItem = .Controls.Add(Type:=msoControlButton)
With ThisMenuItem
.Caption = "UPDATE"
.OnAction = "UPDATE"
End With
'sort sheet data by status
Set ThisMenuItem = .Controls.Add(Type:=msoControlButton)
With ThisMenuItem
.BeginGroup = True
.Caption = "SORT STATUS"
.OnAction = "sort_1"
End With

'sort sheet data by item
Set ThisMenuItem = .Controls.Add(Type:=msoControlButton)
With ThisMenuItem
.BeginGroup = True
.Caption = "SORT ITEM"
.OnAction = "sort_2"
End With

'sort sheet data by customer
Set ThisMenuItem = .Controls.Add(Type:=msoControlButton)
With ThisMenuItem
.BeginGroup = True
.Caption = "SORT CUSTOMER"
.OnAction = "sort_3"
End With


'sort sheet data by activity
Set ThisMenuItem = .Controls.Add(Type:=msoControlButton)
With ThisMenuItem
.BeginGroup = True
.Caption = "SORT ACTIVITY"
.OnAction = "sort_4"
End With


End With
End If
End Sub
Sub auto2()
With ActiveWorkbook
.RunAutoMacros xlAutoClose
.Close
End With
End Sub
'remove menu sub
Sub auto_close()
Dim CncMenu As Object
Set CncMenu = CommandBars.FindControl(Tag:="CNC-Menu")
' If Not CncMenu Is Nothing Then
CncMenu.Delete
' End If
End Sub

 

[Harald Staff]

Hi Jeff

Excel 2007 doesn't have menus, or toolbars. So the answer isn't a simple
"check that setting here". But see if you can find something inspiring on
Ron's site http://www.rondebruin.nl/tips.htm

HTH. Best wishes Harald

 

[Ron de Bruin]

See the 2007 section on my site for examples
Do you not see your menu items in the ribbon on the Add-ins tab

 

[Fred Smif]

Hi Jeff

Excel 2007 doesn't have menus, or toolbars. So the answer isn't a simple
"check that setting here". But see if you can find something inspiring on
Ron's site http://www.rondebruin.nl/tips.htm

HTH. Best wishes Harald

Does anyone find that "ribbon" useful? I find that my 10 years collection of
custom menus are now useless.

 

[Harald Staff]

Does anyone find that "ribbon" useful? I find that my 10 years collection
of custom menus are now useless.

Why did you upgrade ?

 

[Jeff]

Thanks for the information, I went to Rons link and since I kept getting
"jacked" on a macro security issue, I edited the regestry like in the
example
adding the D word and now they do show up, but yes they are a little out of,
veiw but better than nothing.

Thank for the help,,,

<Jeff>

 

[Tim Williams]

http://blogs.msdn.com/jensenh/archive/2008/03/12/the-story-of-the-ribbon.aspx

Tim