这是我之前做的一个菜单,比较长,你看下,
Sub AddToolbar()
Dim arr As Variant
Dim id As Variant
Dim i As Integer
Dim Toolbar As CommandBar
On Error Resume Next
Application.CommandBars("MyToolbar").Delete
arr = Array("五线白玻", "六线白玻", "二线Low_e", "二线阳光", "二线Solar_kb", "二线TCO", "二线超白TCO", "二线过渡白", "二线普白", "三线白玻", "三线普白", "三线过渡白", "三线超白", "四线白玻", "一线拉引量", "二线拉引量", "二线白玻拉引量", "二线Low_e拉引量", "二线阳光拉引量", "二线Solar_kb拉引量", "二线TCO拉引量", "二线超白TCO拉引量", "二线过渡白拉引量", "二线普白拉引量", "三线拉引量", "三线超白拉引量", "三线过渡白拉引量", "三线普白拉引量", "三线白玻拉引量", "四线拉引量", "一线原料配料表", "二线原料配料表", "三线原料配料表", "四线原料配料表", "原料干基量", "原料湿基量", "原料耗用量", "原料配料表", "重油", "生产经营日报表", "漳州日报表", "二线明细日报表", "三线明细日报表", "OA日报表新", "销量汇总", "产量汇总", "集架汇总", "产量规格明细汇总", "一线班产量", "二线班产量", "三线班产量", "四线班产量", "产量零片", "产销量查询")
Set Toolbar = Application.CommandBars.Add("MyToolbar", msoBarTop)
'Set Toolbar = Application.CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlPopup, before:=11)
With Toolbar
.Protection = msoBarNoResize
.Visible = True
With .Controls.Add(msoControlPopup)
.Caption = "第五生产线"
.BeginGroup = True
i = 0
With .Controls.Add(Type:=msoControlButton)
.Caption = arr(i)
.BeginGroup = True
.Style = msoButtonCaption '修改这句
.OnAction = "表" & i + 1 '加上这句
End With
End With
With .Controls.Add(msoControlPopup)
.Caption = "第六生产线"
.BeginGroup = True
For i = 1 To 8
With .Controls.Add(Type:=msoControlButton)
.Caption = arr(i)
.BeginGroup = True
.Style = msoButtonCaption '修改这句
.OnAction = "表" & i + 1 '加上这句
End With
Next
End With
With .Controls.Add(msoControlPopup)
.Caption = "第三生产线"
.BeginGroup = True
For i = 9 To 12
With .Controls.Add(Type:=msoControlButton)
.Caption = arr(i)
.BeginGroup = True
.Style = msoButtonCaption '修改这句
.OnAction = "表" & i + 1 '加上这句
End With
Next
End With
With .Controls.Add(msoControlPopup)
.Caption = "第四生产线"
.BeginGroup = True
i = 13
With .Controls.Add(Type:=msoControlButton)
.Caption = arr(i)
.BeginGroup = True
.Style = msoButtonCaption '修改这句
.OnAction = "表" & i + 1 '加上这句
End With
End With
With .Controls.Add(msoControlPopup)
.Caption = "生产线拉引量"
.BeginGroup = True
For i = 14 To 29
With .Controls.Add(Type:=msoControlButton)
.Caption = arr(i)
.BeginGroup = True
.Style = msoButtonCaption '修改这句
.OnAction = "表" & i + 1 '加上这句
End With
Next
End With
With .Controls.Add(msoControlPopup)
.Caption = "原料配料表"
.BeginGroup = True
For i = 30 To 37
With .Controls.Add(Type:=msoControlButton)
.Caption = arr(i)
.BeginGroup = True
.Style = msoButtonCaption '修改这句
.OnAction = "表" & i + 1 '加上这句
End With
Next
End With
With .Controls.Add(msoControlPopup)
.Caption = "重油"
.BeginGroup = True
i = 38
With .Controls.Add(Type:=msoControlButton)
.Caption = arr(i)
.BeginGroup = True
.Style = msoButtonCaption '修改这句
.OnAction = "表" & i + 1 '加上这句
End With
End With
With .Controls.Add(msoControlPopup)
.Caption = "生产经营日报表"
.BeginGroup = True
For i = 39 To 43
With .Controls.Add(Type:=msoControlButton)
.Caption = arr(i)
.BeginGroup = True
.Style = msoButtonCaption '修改这句
.OnAction = "表" & i + 1 '加上这句
End With
Next
End With
With .Controls.Add(msoControlPopup)
.Caption = "产销汇总表"
.BeginGroup = True
For i = 44 To 47
With .Controls.Add(Type:=msoControlButton)
.Caption = arr(i)
.BeginGroup = True
.Style = msoButtonCaption '修改这句
.OnAction = "表" & i + 1 '加上这句
End With
Next
End With
With .Controls.Add(msoControlPopup)
.Caption = "各线班产量"
.BeginGroup = True
For i = 48 To 51
With .Controls.Add(Type:=msoControlButton)
.Caption = arr(i)
.BeginGroup = True
.Style = msoButtonCaption '修改这句
.OnAction = "表" & i + 1 '加上这句
End With
Next
End With
With .Controls.Add(msoControlPopup)
.Caption = "产量零片"
.BeginGroup = True
i = 52
With .Controls.Add(Type:=msoControlButton)
.Caption = arr(i)
.BeginGroup = True
.Style = msoButtonCaption '修改这句
.OnAction = "表" & i + 1 '加上这句
End With
End With
With .Controls.Add(msoControlPopup)
.Caption = "查询"
.BeginGroup = True
i = 53
With .Controls.Add(Type:=msoControlButton)
.Caption = arr(i)
.BeginGroup = True
.Style = msoButtonCaption '修改这句
.OnAction = "表" & i + 1 '加上这句
End With
End With
End With
Set Toolbar = Nothing
End Sub
Sub delAddToolbar()
Application.CommandBars("MyToolbar").Delete
End Sub