Adding Menu Items to excel 2007
 

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

Adding Menu Items to excel 2007
 

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


"Jeff" wrote in message
...
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

Adding Menu Items to excel 2007
 

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

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Jeff" wrote in message ...
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

Adding Menu Items to excel 2007
 

"Harald Staff" wrote in message
...
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. :(

Adding Menu Items to excel 2007
 

"Fred Smif" wrote in message
...

"Harald Staff" wrote in message
...
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. :(

Why did you upgrade ?

Adding Menu Items to excel 2007
 

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




"Ron de Bruin" wrote in message
...
See the 2007 section on my site for examples
Do you not see your menu items in the ribbon on the Add-ins tab
--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Jeff" wrote in message
...
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

OT - Adding Menu Items to excel 2007
 

http://blogs.msdn.com/jensenh/archiv...he-ribbon.aspx

Tim


"Harald Staff" wrote in message
...
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


"Jeff" wrote in message
...
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