[已解决]关于CommandBarButton中的命令按钮返回值

xyz8462

我想制作一个CommandBarButton命令集合，集合里面是把当前工作表的表名放进去，通过点击某个按钮就可以直接选择对应的工作表


代码是
Sub zid()
On Error Resume Next
  Dim x, copup As CommandBarPopup, copup1 As CommandBarButton, m
  Application.CommandBars("CELL").Controls("工作表").Delete
  Set copup = Application.CommandBars("cell").Controls.Add(Type:=msoControlPopup, before:=1) '添加到最上的位置
   With copup
     .Caption = "工作表"
     .BeginGroup = True '添加分组线
   End With
  
   For m = 1 To ActiveWorkbook.Worksheets.Count
    With copup1
    Set copup1 = copup.Controls.Add(before:=m)  '添加到最上的位置
     .Caption = ActiveWorkbook.Worksheets(m).Name
     .FaceId = m
     .Style = msoButtonCaption '图标和文字的显示
     .OnAction = "选择"
      End With
       Next m
End Sub

问题：现在就是不知道如何得到我所点击的按钮的值，CommandBarButton按钮的ONACTION属性是
sub 选择（）
sheets(？ ).select
end sub
     怎么来表示呢？

最佳答案

月排行榜 / 总排行榜

甲百

2013-7-12 17:10

模块中
  Dim arr() As New 类1
Sub zid()
On Error Resume Next
  Dim x, copup As CommandBarPopup, copup1 As CommandBarButton, m
  Application.CommandBars("CELL").Controls("工作表").Delete
  ReDim arr(1 To 1)
  Set copup = Application.CommandBars("cell").Controls.Add(Type:=msoControlPopup, before:=1) '添加到最上的位置
   With copup
     .Caption = "工作表"
     .BeginGroup = True '添加分组线
   End With
  
   For m = 1 To ActiveWorkbook.Worksheets.Count
   
    Set copup1 = copup.Controls.Add(before:=m)  '添加到最上的位置
    With copup1
     .Caption = ActiveWorkbook.Worksheets(m).Name
     .FaceId = m
     .Style = msoButtonCaption '图标和文字的显示
     k = k + 1
     ReDim Preserve arr(1 To k)
     Set arr(k).cbar = copup1
    End With

   Next m
End Sub
类模块(名子为: 类1)
Option Explicit
Public WithEvents cbar As CommandBarButton

Private Sub cbar_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
   Sheets(cbar.Caption).Select
End Sub
工作簿1.zip (16.07 KB, 下载次数: 65)

2013-7-12 17:10 上传

点击文件名下载附件

跳转到最佳答案楼层

甲百

实现这个要用类模块才可以

甲百

模块中
  Dim arr() As New 类1
Sub zid()
On Error Resume Next
  Dim x, copup As CommandBarPopup, copup1 As CommandBarButton, m
  Application.CommandBars("CELL").Controls("工作表").Delete
  ReDim arr(1 To 1)
  Set copup = Application.CommandBars("cell").Controls.Add(Type:=msoControlPopup, before:=1) '添加到最上的位置
   With copup
     .Caption = "工作表"
     .BeginGroup = True '添加分组线
   End With
  
   For m = 1 To ActiveWorkbook.Worksheets.Count
   
    Set copup1 = copup.Controls.Add(before:=m)  '添加到最上的位置
    With copup1
     .Caption = ActiveWorkbook.Worksheets(m).Name
     .FaceId = m
     .Style = msoButtonCaption '图标和文字的显示
     k = k + 1
     ReDim Preserve arr(1 To k)
     Set arr(k).cbar = copup1
    End With

   Next m
End Sub
类模块(名子为: 类1)
Option Explicit
Public WithEvents cbar As CommandBarButton

Private Sub cbar_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
   Sheets(cbar.Caption).Select
End Sub
工作簿1.zip (16.07 KB, 下载次数: 65)

2013-7-12 17:10 上传

点击文件名下载附件