Excel vba 批量创建按钮代码示例
在excel模块中的代码：
‘运行代码，自动批量生成ActiveX按钮插件:
Sub ReplaceLineButton_2()
    UpdateLinesButton_4
    Dim WSheet As Worksheet
    Dim MyNewbtn As OLEObject
    Dim Target As Range
    Dim ShtCodeName As String
    Dim i As Integer
    Dim j As Integer
    i = 0: j = 50
    Set WSheet = Worksheets("Sheet1")
    ThisWorkbook.ActiveSheet.Columns(1).ColumnWidth = 12.6
    For i = 1 To 10
        Set Target = Cells(i + 1, 1)
        ActiveSheet.OLEObjects.Delete
        'ActiveSheet.Range(i + 1, 1).EntireRow.Delete
        ThisWorkbook.ActiveSheet.Rows(i).RowHeight = 20
        Set MyNewbtn = WSheet.OLEObjects.Add(ClassType:= _
        "Forms.CommandButton.1", Link:=False _
            , DisplayAsIcon:=False, Left:=Target.Left, _
            Top:=Target.Top, Width:=80, Height:=20)
        MyNewbtn.Name = "MyNewButton" + CStr(i+50)
        MyNewbtn.Object.Caption = "我的按钮" + CStr(i)
        ShtCodeName = WSheet.CodeName
        With ThisWorkbook.VBProject.VBComponents.Item(ShtCodeName).CodeModule
            j = j + 1
            .ReplaceLine j, "Private Sub MyNewButton" + CStr(i + 50) + "_Click()"
            j = j + 1
            .ReplaceLine j, "msgbox ""生成事件成功"""
            j = j + 1
            .ReplaceLine j, "End Sub"
        End With
    Next i
End Sub
在excel文件Sheet1表中宏代码：
Sub CommandButton1_Click()
    CommandButton1.Visible = False
    CommandButton2.Visible = True
    CommandButton3.Visible = True
    Call InsertLinesButton_1
End Sub
Sub CommandButton2_Click()
    CommandButton1.Visible = False
    CommandButton2.Visible = True
    CommandButton3.Visible = True
    Call ReplaceLineButton_2
End Sub
Sub CommandButton3_Click()
    CommandButton1.Visible = False
    CommandButton2.Visible = False
    CommandButton3.Visible = False
    Call DeleteLinesButton_3
End Sub
Sub CommandButton4_Click()
    CommandButton3.Visible = True
    CommandButton2.Visible = True
    CommandButton1.Visible = True
End Sub
'25
'26
'27
'28
'29
'30
'31
'32
'33
'34
'35
'36
'37
'38
resize函数c++
'39
'40
'41
'42
'43
'44
'45
'46
'47
'48
'49
'50---------------------------------------
' ‘
'---------------------------------------把这段复制到当前Sheet宏中
'
Excel文件设置Sheet1表ActiveX按钮插件：
效果图片：
批量增加复选框：
Dim i as integer
For i to 10
ActiveSheet.CheckBoxes.Add(Cells(i, 1).Left, Cells(i, 1).Top, 24, 16.5).Select
Next i
批量增加按钮，而非ActiveX按钮插件：
Dim i as integer
For i to 10
ActiveSheet.Buttons.Add(Cells(i, 1).Left, Cells(i, 1).Top, 24, 16.5).Select
Next i
EXCEL VBA常用语句：
定制模块行为
(1) Option Explicit '强制对模块内所有变量进行声明
Option Private Module '标记模块为私有，仅对同一工程中其它模块有用，在宏对话框中不显示
      Option Compare Text '字符串不区分大小写
      Option Base 1 '指定数组的第一个下标为1
(2) On Error Resume Next '忽略错误继续执行VBA代码,避免出现错误消息
(3) On Error GoTo ErrorHandler '当错误发生时跳转到过程中的某个位置
(4) On Error GoTo 0 '恢复正常的错误提示
(5) Application.DisplayAlerts=False '在程序执行过程中使出现的警告框不显示
(6) Application.ScreenUpdating=False '关闭屏幕刷新
  Application.ScreenUpdating=True '打开屏幕刷新
(7) Application.Enable.CancelKey=xlDisabled '禁用Ctrl+Break中止宏运行的功能
工作簿
(8) Workbooks.Add() '创建一个新的工作簿
(9) Workbooks(“book1.xls”).Activate '激活名为book1的工作簿
(10) ThisWorkbook.Save '保存工作簿
(11) ThisWorkbook.close '关闭当前工作簿
(12) ActiveWorkbook.Sheets.Count '获取活动工作薄中工作表数
(13) ActiveWorkbook.name '返回活动工作薄的名称
(14) ThisWorkbook.Name ‘返回当前工作簿名称
    ThisWorkbook.FullName ‘返回当前工作簿路径和名称
(15) ActiveWindow.EnableResize=False ‘禁止调整活动工作簿的大小
(16) Application.Window.Arrange xlArrangeStyleTiled ‘将工作簿以平铺方式排列
(17) ActiveWorkbook.WindowState=xlMaximized ‘将当前工作簿最大化
工作表
(18) ActiveSheet.UsedRange.Rows.Count ‘当前工作表中已使用的行数
(19) Rows.Count ‘获取工作表的行数(注：考虑向前兼容性)
(20) Sheets(Sheet1).Name= “Sum” '将Sheet1命名为Sum
(21) ThisWorkbook.Sheets.Add Before:=Worksheets(1) '添加一个新工作表在第一工作表前
(22) ActiveSheet.Move After:=ActiveWorkbook. _
Sheets(ActiveWorkbook.Sheets.Count) '将当前工作表移至工作表的最后
(23) Worksheets(Array(“sheet1”,”sheet2”)).Select '同时选择工作表1和工作表2
(24) Sheets(“sheet1”).Delete或 Sheets(1).Delete '删除工作表1
(25) ActiveWorkbook.Sheets(i).Name '获取工作表i的名称
(26) ActiveWindow.DisplayGridlines=Not ActiveWindow.DisplayGridlines '切换工作表中的网格线显示，这种方法也可以用在其它方面进行相互切换，即相当于开关按钮
(27) ActiveWindow.DisplayHeadings=Not ActiveWindow.DisplayHeadings ‘切换工作表中的行列边框显示
(28) ActiveSheet.UsedRange.FormatConditions.Delete ‘删除当前工作表中所有的条件格式
(29) Cells.Hyperlinks.Delete ‘取消当前工作表所有超链接
(30) ActiveSheet.PageSetup.Orientation=xlLandscape
或ActiveSheet.PageSetup.Orientation=2 '将页面设置更改为横向
(31) ActiveSheet.PageSetup.RightFooter=ActiveWorkbook.FullName ‘在页面设置的表尾中输入文件路径
ActiveSheet.PageSetup.LeftFooter=Application.UserName ‘将用户名放置在活动工作表的页脚
单元格/单元格区域
(32) ActiveCell.CurrentRegion.Select
或Range(ActiveCell.End(xlUp),ActiveCell.End(xlDown)).Select
'选择当前活动单元格所包含的范围，上下左右无空行
(33) Cells.Select ‘选定当前工作表的所有单元格

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。