|
本帖最后由 苏钊丶 于 2019-12-20 08:58 编辑
- Sub main() '根据数据表初始化弹出菜单
- Dim mybar As CommandBar, arr, i&, d
- On Error Resume Next
- Application.CommandBars("myCell").Delete '重设菜单前删除原菜单
- Set mybar = Application.CommandBars.Add(Name:="myCell", Position:=msoBarPopup) '创建弹出式菜单
- arr = Range("Data!A1").CurrentRegion.Value '定位数据区,源数据放入数组arr
- Set d = CreateObject("Scripting.Dictionary")
- For i = 2 To UBound(arr) '遍历数据源行,从第2行开始
- Call 菜单(arr, i, 1, d, mybar)
- Next
- Set d = Nothing
- Set mybar = Nothing
- End Sub
- Sub 菜单(arr, i, n, ByVal d, ByVal myb)
- '参数 arr-源数据数组,i-,n-,d-字典,myB-菜单
- Dim x, y
- x = arr(i, n) '源数组第i行第N列
- y = WorksheetFunction.CountA(Application.Index(arr, i)) '当前行的元素个数
- If Not d.Exists(x) Then '如果字典中关键字x不存在,即当前关键字未添加进菜单
- If n = y Then '如果当前列数n等于arr源数组的最大列数,即到达最后一级
- d.Add x, i '在字典d中以x为key,存为值i
- 'd(x) = i
- With myb.Controls.Add(Type:=msoControlButton) '菜单加入触发按钮
- .Caption = x '菜单按钮名称为x
- .OnAction = "输入(" & i & "," & n & ")" '最后一级选择触发事件,完成输入
- ' Debug.Print x; "输入(" & i & "," & n & ")"
- End With
- Else '如果不是最后一级菜单,则继续添加字典及菜单
- Set d(x) = CreateObject("Scripting.Dictionary")
- Set myb = myb.Controls.Add(Type:=msoControlPopup) '加入下级弹出菜单
- myb.Caption = x '菜单按钮名称为x
- End If
- Else '如果字典key已存在,菜单即引用本级内x关键字菜单
- Set myb = myb.Controls.Item(x)
- End If
-
- If n < y Then '如果当前列未到达源数据最大列
- Call 菜单(arr, i, n + 1, d(x), myb) '递归调用本过程继续生成菜单
- End If
- End Sub
- Sub 输入(i, m)
- Dim arr
- arr = Worksheets("Data").Range("A" & i).Resize(1, m).Value
- 'ActiveCell.EntireRow.Range("A1").Resize(1, 4).ClearContents
- ActiveCell.EntireRow.Range("A1").Resize(1, m) = arr
- End Sub
- Public Sub SubPopBar(keys() As Variant)
- '根据参数数组返回子菜单,并复制到单独的弹出菜单
- Dim intI As Integer, subB
- Dim mybar As CommandBar
- Set subB = CommandBars("myCell")
- On Error Resume Next
- For intI = 0 To UBound(keys) '获得参数列表的子菜单
- If keys(intI) <> "" Then
- Set subB = subB.Controls(keys(intI))
- Else
- Application.CommandBars("myCell").ShowPopup '如果前面几列输入的数据为空则直接弹出顶级菜单
- Exit Sub
- End If
- Next intI
- On Error Resume Next
- Application.CommandBars("myCellx").Delete '重设菜单前删除原菜单
- Set mybar = Application.CommandBars.Add(Name:="myCellx", Position:=msoBarPopup) '创建弹出式菜单
-
- For intI = 1 To subB.Controls.Count
- subB.Controls(intI).Copy Bar:=mybar '从顶级菜单中摘出需要的子菜单
- Next
- Set subB = Nothing
- Set mybar = Nothing
- Application.CommandBars("myCellx").ShowPopup
- End Sub
复制代码
|
|