J
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
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