|
本帖最后由 hefeilac 于 2017-4-24 15:26 编辑
此部分代码借用论坛上的,在此感谢原创老师
附件为excel格式,并可正常执行
求助.zip
(56.4 KB, 下载次数: 4)
现在我用VB封装后总是弹出
貌似WriteToRng过程出现了问题
我的VB封装代码如下,请老师指正,不胜感激!
我在过程中call main是可以显示弹出式菜单,只有最后输出到表格时出错!
- Public xlapp As Excel.Application
- Dim Tree
- Sub Main()
- Dim mybar As Object, arr, i&, j&, key$, myb, pkey$
- Dim N_col As Long
- Dim wb As Workbook
- On Error Resume Next
- Set wb = GetObject(App.Path & "\紧固件三级选单.xls")
- Set Tree = CreateObject("Scripting.Dictionary")
- xlapp.CommandBars("myCell").Delete
- Set mybar = xlapp.CommandBars.Add(Name:="myCell", Position:=msoBarPopup)
- Tree.Add "myCell", mybar
- arr = wb.Worksheets("sheet1").Range("a1").CurrentRegion.Value
- N_col = UBound(arr, 2)
- ReDim Preserve arr(1 To UBound(arr, 1), 1 To N_col + 1)
- For j = 2 To UBound(arr, 1)
- If Not Tree.Exists(arr(j, 1)) Then
- If arr(j, 2) = "" Then
- AddControlButton "myCell", arr(j, 1), arr(j, 1), j, N_col
- Else
- AddControlPopup "myCell", arr(j, 1), arr(j, 1)
- End If
- End If
- Next
- For i = 2 To UBound(arr)
- key = arr(i, 1)
- For j = 2 To N_col
- If arr(i, j) <> "" Then
- pkey = key
- key = key & "" & arr(i, j)
- If arr(i, j + 1) = "" Then
- AddControlButton pkey, key, arr(i, j), i, N_col
- Else
- If Not Tree.Exists(key) Then
- AddControlPopup pkey, key, arr(i, j)
- End If
- End If
- End If
- Next
- Next
- wb.Close False
- Set wb = Nothing
- Set mybar = Nothing
- End Sub
- Private Sub AddControlButton(ByVal pkey$, ByVal key$, ByVal caption$, ByVal i&, ByVal n&)
- Dim myb
- Set myb = Tree(pkey).Controls.Add(Type:=msoControlButton)
- With myb
- .caption = caption
- .OnAction = "'WriteToRng " & i & "," & n & "'"
- End With
- Tree.Add key, myb
- End Sub
- Private Sub AddControlPopup(ByVal pkey$, ByVal key$, ByVal caption$)
- Dim myb
- Set myb = Tree(pkey).Controls.Add(Type:=msoControlPopup)
- myb.caption = caption
- Tree.Add key, myb
- End Sub
- Public Sub WriteToRng(i, N_col) '写入单元格
- xlapp.ScreenUpdating = False
- Dim wb As Workbook
- Set wb = GetObject(App.Path & "\紧固件三级选单.xls")
- Set sh = wb.Worksheets("sheet1")
- xlapp.ActiveCell = sh.Cells(i, N_col).Value
- wb.Close
- Set wb = Nothing
- xlapp.ScreenUpdating = True
- End Sub
复制代码
|
|