怎样使用VBA制作三级菜单？

1、首先在开发工具中打开VBA编辑器

2、在单元格区域当中输入一些内容作为例子

3、在VBA编辑器中插入模块

4、在模块当中输入如下代码，然后运行

Sub建立三级菜单()

 

'On Error Resume Next

 

    Dim Menu As CommandBarControl, SubMenu As CommandBarControl, SubMenus

 

    As CommandBarControl

 

    Dim i, j

 

    Application.CommandBars(1).Controls("IT大学(&I)").Delete

 

    Set Menu=Application.CommandBars(1).Controls.Add(msoControlPopup,, , , True)

 捧速够

    Menu.Caption="IT大学(&I)"

 

    '********************一级菜单按钮***********************

 

    With Menu.Controls.Add(msoControlButton, 1, , , True)

 

      .Caption="辅导班" ' & WorksheetFunction.Text(i, " [DBNum2][$-804]0")

 

      .OnAction="andy"

 

      .FaceId=481

 

    End With

 

'*********************一级下拉菜单**********************

 

For i=1 To 3

 

  Set SubMenu=Menu.Controls.Add(msoControlPopup, 1, , , True)

 

  SubMenu.Caption=WorksheetFunction.Text(i, "[DBNum1][$-804]0") & "年级"

 

  SubMenu.BeginGroup=True

 

 Next i

 

'*********************二级下拉菜单**********************

 

For i=1 To 4

 

  Set SubMenus=Menu.Controls("一年级").Controls.Add(msoControlPopup, 1, , , True)

 

  SubMenus.Caption=WorksheetFunction.Text(i, "[DBNum1][$-804]0") & "班"

Next i

 

For i=1 To 3

 

  Set SubMenus=Menu.Controls("二年级")

 

  .Controls.Add(msoControlPopup, 1, , , True)

 

  SubMenus.Caption=WorksheetFunction.Text(i, "[DBNum1][$-804]0") & "班"

 

 Next i

 

For i=1 To 3

 

Set SubMenus=Menu.Controls("三年级").Controls.Add(msoControlPopup, 1, , , True)

 

  SubMenus.Caption=WorksheetFunction.Text(i, "[DBNum1][$-804]0") &肺称 "班"

 

 Next i

 

'*********************三级菜单按钮**********************

 

 For i=1 To 3

 

 Set SubMenus=Menu.Controls("一年级").Controls("一班").Controls.

 

  Add(msoControlButton, 1, , , True)

 

  SubMenus.Caption=Chr(64+i) & "组"

 

  SubMenus.Style=msoButtonIconAndCaption

 

  SubMenus.FaceId=70+i

 

 Next i

 

 For i=1 To 3

 

 Set SubMenus=Menu.Controls("物良一年级").Controls("二班").Controls.

 

  Add(msoControlButton, 1, , , True)

 

  SubMenus.Caption=Chr(64+i) & "组"

 

  SubMenus.Style=msoButtonIconAndCaption

 

  SubMenus.FaceId=70+i

 

 Next i

 

 For i=1 To 2

 

 Set SubMenus=Menu.Controls("一年级").Controls("三班").Controls.

 

  Add(msoControlButton, 1, , , True)

 

  SubMenus.Caption=Chr(64+i) & "组"

 

  SubMenus.Style=msoButtonIconAndCaption

 

  SubMenus.FaceId=70+i

 

 Next i

 

 For i=1 To 3

 

 Set SubMenus=Menu.Controls("一年级").Controls("四班").Controls.

 

  Add(msoControlButton, 1, , , True)

 

  SubMenus.Caption=Chr(64+i) & "组"

 

  SubMenus.Style=msoButtonIconAndCaption

 

  SubMenus.FaceId=70+i

 

 Next i

 

  For i=1 To 3

 

  Set SubMenus=Menu.Controls("二年级").Controls("一班").Controls.

Add(msoControlButton, 1, , , True)

 

  SubMenus.Caption=Chr(64+i) & "组"

 

  SubMenus.Style=msoButtonIconAndCaption

 

  SubMenus.FaceId=70+i

 

Next i

 

For i=1 To 4

 

Set SubMenus=Menu.Controls("二年级").Controls("二班").Controls.

 

  Add(msoControlButton, 1, , , True)

 

 SubMenus.Caption=Chr(64+i) & "组"

 

 SubMenus.Style=msoButtonIconAndCaption

 

 SubMenus.FaceId=70+i

 

Next i

 

For i=1 To 3

 

Set SubMenus=Menu.Controls("二年级").Controls("三班").Controls.

 

  Add(msoControlButton, 1, , , True)

 

 SubMenus.Caption=Chr(64+i) & "组"

 

 SubMenus.Style=msoButtonIconAndCaption

 

 SubMenus.FaceId=70+i

 

Next i

 

For i=1 To 3

 

  Set SubMenus=Menu.Controls("三年级").Controls("一班").Controls.

 

Add(msoControlButton, 1, , , True)

 

 SubMenus.Caption=Chr(64+i) & "组"

 

 SubMenus.Style=msoButtonIconAndCaption

 

 SubMenus.FaceId=70+i

 

Next i

 

 For i=1 To 3

 

  Set SubMenus=Menu.Controls("三年级").Controls("二班").Controls.

 

Add(msoControlButton, 1, , , True)

 

 SubMenus.Caption=Chr(64+i) & "组"

 

 SubMenus.Style=msoButtonIconAndCaption

 

 SubMenus.FaceId=70+i

 

Next i

 

For i=1 To 3

 

  Set SubMenus=Menu.Controls("三年级").Controls("三班").Controls.

 

Add(msoControlButton, 1, , , True)

 

 SubMenus.Caption=Chr(64+i) & "组"

 

 SubMenus.Style=msoButtonIconAndCaption

 

 SubMenus.FaceId=70+i

 

Next i

 

End Sub

5、用快捷键F5运行程序，工作簿的【加载项】功能区立即出现刚生成的三级菜单，如图