之前《通过VBA制造EXCEL的菜单》的程序代码做了一大堆无用功，今天发现原来可以以这样更简单更好的代码来实现：

1。新建EXCEL文件，按“Alt”＋“F11”进入VB编辑器，点“ThisWorkBook”打开空白窗口，在其中输入以下代码：

Const LineNum As Integer = 23 '按钮行数
Const ListNum As Integer = 44 '按钮列数

Private Sub Workbook_BeforeClose(Cancel As Boolean)
For jj = 1 To LineNum
  DelName = "菜单" & jj
  On Error Resume Next
  Application.CommandBars(DelName).Delete
Next
End Sub

Private Sub Workbook_Open()
For jj = 1 To LineNum
  AddBars jj
Next
End Sub

Public Sub AddBars(BarIndex)
Dim tbar As CommandBar
BarName = "菜单" & BarIndex
Set tbar = Application.CommandBars.Add(Name:=BarName, Position:=msoBarBottom)
tbar.Visible = True
For k = 1 To ListNum
  On Error Resume Next
  AddButton BarIndex, k, BarName
Next
End Sub

Public Sub AddButton(BarIndex, BtnIndex, BarName)
Dim Btn As CommandBarButton
ii = BarIndex * ListNum + BtnIndex
ButtonName = "按钮" & ii
Set Btn = Application.CommandBars(BarName).Controls.Add
With Btn
  .TooltipText = "ID:" & ii
  .FaceId = ii
End With
End Sub

保存文件后退出，再重新打开此EXCEL文件，点击“启用宏”按钮。出现一个壮观的画面。

评论