星期六, 二月 03, 2007

Tips EXCEL VBA里如何增加菜单及菜单项目风格线

在vbproject ThisWorkbook 里输入以下代码

Sub AddMenus()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl

Set cbMainMenuBar = _
Application.CommandBars("Worksheet Menu Bar")

Set cbcCutomMenu = _
cbMainMenuBar.Controls.Add(Type:=msoControlPopup)

cbcCutomMenu.Caption = "&Q功能菜单"

With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "简约方式"
.OnAction = "forcaseGS3"
End With

With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "简约方式MOD"
.OnAction = "forcaseGSMod3"

End With

With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "打印机设置"
.OnAction = "printerSetup"
.BeginGroup = True '在这个菜单前产生一个分割线
End With


End Sub


Sub DeleteMenu()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&Q功能菜单").Delete
On Error GoTo 0
End Sub


Private Sub Workbook_Activate()
AddMenus
End Sub


Private Sub Workbook_Deactivate()
DeleteMenu
End Sub