创建级联菜单 Public Class Connect Class Connect Implements Extensibility.IDTExtensibility2 Dim app As Excel.Application Dim addInInstance As Object Dim mainMenuBar As Microsoft.Office.Core.CommandBar Dim newEntryBar As Microsoft.Offic
创建级联菜单
Public
Class Connect
Class Connect
Implements Extensibility.IDTExtensibility2
Dim app As Excel.Application
Dim addInInstance As Object
Dim mainMenuBar As Microsoft.Office.Core.CommandBar
Dim newEntryBar As Microsoft.Office.Core.CommandBarPopup
Dim newSubBar As Microsoft.Office.Core.CommandBarPopup
Dim subMenu1 As Microsoft.Office.Core.CommandBarButton
Dim addPosition As Integer
Public Sub OnBeginShutdown()Sub OnBeginShutdown(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnBeginShutdown
End Sub
Public Sub OnAddInsUpdate()Sub OnAddInsUpdate(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnAddInsUpdate
End Sub
Public Sub OnStartupComplete()Sub OnStartupComplete(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnStartupComplete
End Sub
Public Sub OnDisconnection()Sub OnDisconnection(ByVal RemoveMode As Extensibility.ext_DisconnectMode, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnDisconnection
End Sub
Public Sub OnConnection()Sub OnConnection(ByVal application As Object, ByVal connectMode As Extensibility.ext_ConnectMode, ByVal addInInst As Object, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnConnection
app = CType(application, Excel.Application)
addInInstance = addInInst
addMenu()
End Sub
Public Sub addMenu()Sub addMenu()
Dim wb As Excel.Workbook
If app.Workbooks.Count > 0 Then
wb = app.ActiveWorkbook
Else
wb = app.Workbooks.Add
End If
Dim ws As Excel.Worksheet
If wb.Worksheets.Count > 0 Then
ws = wb.ActiveSheet
Else
ws = wb.Worksheets.Add
End If
mainMenuBar = CType(app.CommandBars.ActiveMenuBar, Microsoft.Office.Core.CommandBar)
addPosition = mainMenuBar.Controls("编辑(&E)").Index '直接获得 ［编辑］ 菜单的位置
newEntryBar = CType(mainMenuBar.Controls.Add( _
Microsoft.Office.Core.MsoControlType.msoControlPopup, Before:=addPosition, Temporary:=True), Microsoft.Office.Core.CommandBarPopup)
newEntryBar.Caption = "我的菜单(&A)"
newEntryBar.Visible = True
newEntryBar.Enabled = True
newSubBar = CType(newEntryBar.Controls.Add( _
Microsoft.Office.Core.MsoControlType.msoControlPopup, Temporary:=True), _
Microsoft.Office.Core.CommandBarPopup)
newSubBar.Caption = "我的子菜单1"
newSubBar.Visible = True
newSubBar.Enabled = True
subMenu1 = CType(newSubBar.Controls.Add( _
Microsoft.Office.Core.MsoControlType.msoControlButton, Temporary:=True), _
Microsoft.Office.Core.CommandBarButton)
subMenu1.Caption = "我是谁1(&Z)"
subMenu1.Visible = True
subMenu1.Enabled = True
subMenu1.FaceId = 535
AddHandler subMenu1.Click, AddressOf Me.subMenu1Command_Click
newSubBar = CType(newEntryBar.Controls.Add( _
Microsoft.Office.Core.MsoControlType.msoControlPopup, Temporary:=True), _
Microsoft.Office.Core.CommandBarPopup)
newSubBar.Caption = "我的子菜单2"
newSubBar.Visible = True
newSubBar.Enabled = True
subMenu1 = CType(newSubBar.Controls.Add( _
Microsoft.Office.Core.MsoControlType.msoControlButton, Temporary:=True), _
Microsoft.Office.Core.CommandBarButton)
subMenu1.Caption = "我是谁2(&Z)"
subMenu1.Visible = True
subMenu1.Enabled = True
subMenu1.FaceId = 535
AddHandler subMenu1.Click, AddressOf Me.subMenu1Command_Click
End Sub
Private Sub subMenu1Command_Click()Sub subMenu1Command_Click(ByVal Ctrl As Microsoft.Office.Core.CommandBarButton, ByRef CancelDefault As Boolean)
app.ActiveCell.Value = "我是第 " & app.ActiveCell.Row.ToString() & " 行,第 " & app.ActiveCell.Column.ToString & " 列"
End Sub
End Class
注意程序中有 subMenu1.FaceId=535 的代码。这是为菜单添加图标。图标只能添加在叶子上。不能添加到节点上。
但是每个数字到底代表的是什么样子的图标呢？用下面的程序查一下，查看图标及图标代号的程序：
Public
Class Connect
Class Connect
Implements Extensibility.IDTExtensibility2
Dim app As Excel.Application
Dim addInInstance As Object
Dim mainMenuBar As Microsoft.Office.Core.CommandBar
Dim newEntryBar As Microsoft.Office.Core.CommandBarPopup
Dim newSubBar As Microsoft.Office.Core.CommandBarPopup
Dim newSubsubbar As Microsoft.Office.Core.CommandBarPopup
Dim subMenu1 As Microsoft.Office.Core.CommandBarButton
Dim addPosition As Integer
Public Sub OnBeginShutdown()Sub OnBeginShutdown(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnBeginShutdown
End Sub
Public Sub OnAddInsUpdate()Sub OnAddInsUpdate(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnAddInsUpdate
End Sub
Public Sub OnStartupComplete()Sub OnStartupComplete(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnStartupComplete
End Sub
Public Sub OnDisconnection()Sub OnDisconnection(ByVal RemoveMode As Extensibility.ext_DisconnectMode, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnDisconnection
End Sub
Public Sub OnConnection()Sub OnConnection(ByVal application As Object, ByVal connectMode As Extensibility.ext_ConnectMode, ByVal addInInst As Object, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnConnection
app = CType(application, Excel.Application)
addInInstance = addInInst
addMenu()
End Sub
Public Sub addMenu()Sub addMenu()
Dim wb As Excel.Workbook
If app.Workbooks.Count > 0 Then
wb = app.ActiveWorkbook
Else
wb = app.Workbooks.Add
End If
Dim ws As Excel.Worksheet
If wb.Worksheets.Count > 0 Then
ws = wb.ActiveSheet
Else
ws = wb.Worksheets.Add
End If
mainMenuBar = CType(app.CommandBars.ActiveMenuBar, Microsoft.Office.Core.CommandBar)
Dim i As Long
Dim j As Long
Dim k As Long
Dim n As Long = 1
addPosition = mainMenuBar.Controls("编辑(&E)").Index '直接获得 ［编辑］ 菜单的位置
newEntryBar = CType(mainMenuBar.Controls.Add( _
Microsoft.Office.Core.MsoControlType.msoControlPopup, Before:=addPosition, Temporary:=True), Microsoft.Office.Core.CommandBarPopup)
newEntryBar.Caption = "我的菜单(&A)"
newEntryBar.Visible = True
newEntryBar.Enabled = True
For i = 1 To 2
newSubBar = CType(newEntryBar.Controls.Add( _
Microsoft.Office.Core.MsoControlType.msoControlPopup, Temporary:=True), _
Microsoft.Office.Core.CommandBarPopup)
newSubBar.Caption = "我的子菜单" & i.ToString
newSubBar.Visible = True
newSubBar.Enabled = True
For j = 1 To 2
newSubsubbar = CType(newSubBar.Controls.Add( _
Microsoft.Office.Core.MsoControlType.msoControlPopup, Temporary:=True), _
Microsoft.Office.Core.CommandBarPopup)
newSubsubbar.Caption = "我的子菜单" & j.ToString
newSubsubbar.Visible = True
newSubsubbar.Enabled = True
For k = 1 To 2
subMenu1 = CType(newSubsubbar.Controls.Add( _
Microsoft.Office.Core.MsoControlType.msoControlButton, Temporary:=True), _
Microsoft.Office.Core.CommandBarButton)
subMenu1.Caption = "我是谁" & (n).ToString & "(&Z)"
subMenu1.Visible = True
subMenu1.Enabled = True
subMenu1.FaceId = n
AddHandler subMenu1.Click, AddressOf Me.subMenu1Command_Click
n += 1
Next
Next
Next
End Sub
Private Sub subMenu1Command_Click()Sub subMenu1Command_Click(ByVal Ctrl As Microsoft.Office.Core.CommandBarButton, ByRef CancelDefault As Boolean)
app.ActiveCell.Value = "我是第 " & app.ActiveCell.Row.ToString() & " 行,第 " & app.ActiveCell.Column.ToString & " 列"
End Sub
End Class
想看到更多的图标，请改变循环的范围即可。