Excel精英培训网

 找回密码
 注册
数据透视表40+个常用小技巧,让你一次学会!
查看: 2456|回复: 2

[已解决]請幫忙簡化一下添加按鈕的宏

[复制链接]
发表于 2015-1-13 19:19 | 显示全部楼层 |阅读模式
本帖最后由 010203400 于 2015-6-18 08:12 编辑

各位大大, 對于添加工具欄按鈕方面, 我一直是弱項, 七拼八湊的寫了一段宏,
但總覺的如下還可以再簡化, 應當可以寫成1行或2行的, 請大家幫忙指導一下.
Set SubMenu_Command = SubMenu.Controls.Add(Type:=msoControlButton)
SubMenu_Command.OnAction = "Button02"
SubMenu_Command.Caption = "按鈕2"
SubMenu_Command.FaceId = 59

  1. Sub CommandButton1()
  2. On Error Resume Next
  3. Application.CommandBars("GreenDongForWO").Delete

  4. Set GreenDongForWO = Application.CommandBars.Add
  5.     GreenDongForWO.Name = "GreenDongForWO"
  6.     GreenDongForWO.Position = msoBarTop
  7.     GreenDongForWO.MenuBar = True
  8.     GreenDongForWO.Visible = True

  9. Set SubMenu = GreenDongForWO.Controls.Add(Type:=msoControlPopup)
  10.     SubMenu.Caption = "按鈕"

  11. Set SubMenu_Command = SubMenu.Controls.Add(Type:=msoControlButton)
  12.     SubMenu_Command.OnAction = "Button01"
  13.     SubMenu_Command.Caption = "按鈕1"
  14.     SubMenu_Command.FaceId = 59

  15. Set SubMenu_Command = SubMenu.Controls.Add(Type:=msoControlButton)
  16.     SubMenu_Command.OnAction = "Button02"
  17.     SubMenu_Command.Caption = "按鈕2"
  18.     SubMenu_Command.FaceId = 59

  19. ThisWorkbook.Close False
  20. End Sub
复制代码
最佳答案
2015-1-17 12:27
Sub test1()
    Call test2

    '1)创建并设置命令栏
    Set GreenDongForWO = Application.CommandBars.Add
    GreenDongForWO.Name = "GreenDongForWO"
    GreenDongForWO.Position = msoBarTop    '在顶部
    GreenDongForWO.Visible = True

    '2)在该命令栏中,创建弹出框
    Set SubMenu = GreenDongForWO.Controls.Add(Type:=msoControlPopup)
    SubMenu.Caption = "按鈕"

    '3)在该弹出框中,创建命令按钮
    For i = 1 To 2
        Set SubMenu_Command = SubMenu.Controls.Add(Type:=msoControlButton)
        SubMenu_Command.OnAction = "Button0" & i
        SubMenu_Command.Caption = "按鈕" & i
        SubMenu_Command.FaceId = 59
    Next i
End Sub

Sub test2()
    On Error Resume Next
    Application.CommandBars("GreenDongForWO").Delete
    On Error GoTo 0
End Sub


不知情况,如果可能,弹出框中的命令按钮,即第3,可循环


excel精英培训的微信平台,每天都会发送excel学习教程和资料。扫一扫明天就可以收到新教程
发表于 2015-1-17 12:27 | 显示全部楼层    本楼为最佳答案   
Sub test1()
    Call test2

    '1)创建并设置命令栏
    Set GreenDongForWO = Application.CommandBars.Add
    GreenDongForWO.Name = "GreenDongForWO"
    GreenDongForWO.Position = msoBarTop    '在顶部
    GreenDongForWO.Visible = True

    '2)在该命令栏中,创建弹出框
    Set SubMenu = GreenDongForWO.Controls.Add(Type:=msoControlPopup)
    SubMenu.Caption = "按鈕"

    '3)在该弹出框中,创建命令按钮
    For i = 1 To 2
        Set SubMenu_Command = SubMenu.Controls.Add(Type:=msoControlButton)
        SubMenu_Command.OnAction = "Button0" & i
        SubMenu_Command.Caption = "按鈕" & i
        SubMenu_Command.FaceId = 59
    Next i
End Sub

Sub test2()
    On Error Resume Next
    Application.CommandBars("GreenDongForWO").Delete
    On Error GoTo 0
End Sub


不知情况,如果可能,弹出框中的命令按钮,即第3,可循环


回复

使用道具 举报

发表于 2015-1-17 12:35 | 显示全部楼层
Sub test3()
    Dim i
    With Application.CommandBars(1).Controls.Add(Type:=msoControlPopup, temporary:=True)
        .Caption = "弹出框"
        For i = 1 To 5
            With .Controls.Add
                .Caption = "按钮" & i
                .OnAction = "过程" & i
                .FaceId = 59
            End With
        Next i
    End With
End Sub


看情况
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

小黑屋|手机版|Archiver|Excel精英培训 ( 豫ICP备11015029号 )

GMT+8, 2024-3-28 19:44 , Processed in 0.253709 second(s), 5 queries , Gzip On, Yac On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表