办公高手.EXCEL常用宏命令

http://blog.sina.com.cn/s/blog_6f1a235a0100nop5.html

1  设置打开时弹出对话框的命令举例:

    Sub AUTO_OPEN()

    "输入打开工作表时要运行的代码或宏"

    End Bub

       

2  设置关闭时弹出对话框的命令举例:

    Sub AUTO_CLOSE()

    "输入关闭工作表时要运行的代码或宏"

    End Bub

       

3  设置提示框举例:

    MsgBox prompt:="温馨提示：您要打开的工作表有保护！", Buttons:=vbOKOnly, Title:="谷子提示"

       

    i = MsgBox("系统提供不同服务，是否浏览资费信息？", vbYesNoCancel, "谷子提示：")

    If i = vbYes Then

    MsgBox "资费信息：********", , "谷子提示"

    Else

    "输入要运行的代码"

    End If

       

4、提示框内容过长，换行vbNewLine：

    MsgBox "您本次访问系统的时间是：" & Date & " " & Time & vbNewLine & "系统离到期日还剩余：" _

    & DateDiff("d", Now, "2010-12-31") & " 天!" & vbNewLine & "是否查阅到期日说明？", vbYesNoCancel, "时间和到期日提示："

       

5  提示提示系统的日期和时间:

    MsgBox "系统当前日期和时间：" & Date & " " & Time, , "时间提示"

    DateDiff("d", Now, "2010-10-31") & " 天!"   ‘提示离指定日期的剩余天数

       

6  系统时间判断命令:

    Sub 判断时间测试()

    If Date > "2010-05-20" Then

    MsgBox "对不起，测试期间已经结束", , "谷子提示"

    Else

    Sheets("首页").Select  '或其他命令

    End If

    End Sub

       

7  禁止保存或另存的命令:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    MsgBox ("禁止保存")

    Cancel = True '或thisworkbook.saved=true

    End Sub

       

8  保护工作表与撤销保护

    Sheet2.Protect Password:="123456", DrawingObjects:=True, Contents:=True, Scenarios:=True   '保护工作表SHEET2

    Sheet2.Unprotect ("123456")  '解除保护

       

9  保护工作簿与撤销保护

    ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="123"

    ActiveWorkbook.Unprotect Password:="123"

       

10  保护全部工作表

    Sub 保护所有工作表()

    Dim ws As Worksheet

    For Each ws In Worksheets

    ws.Activate

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="123"

    Next

    End Sub

       

11  抽取各表名称

    Sub 抽取各表名称()

    Range("a1").Value = ThisWorkbook.Name

    n = ThisWorkbook.Sheets.Count

    Range("a2").Value = n

    For i = 1 To n

     Cells(i, 2).Value = Sheets(i).Name

    Next

    End Sub

       

    EXCEL2007命令

    Dim n As Variant '指定变量

    Dim i As Integer '指定变量

    Range("a1").Value = ThisWorkbook.Name

    n = ThisWorkbook.Sheets.Count

    Range("a2").Value = n

    For i = 1 To n

     Cells(i, 2).Value = Sheets(i).Name

    Next

       

12、显示/隐藏图表的命令

    ActiveSheet.ChartObjects("图表 1").Visible = Not ActiveSheet.ChartObjects("图表 1").Visible

       

13、显示/隐藏图片的命令

    ActiveSheet.Shapes("Image1").Visible = True

    ActiveSheet.Shapes("Image1").Visible = False

    ActiveSheet.Shapes("Image1").Visible = Not ActiveSheet.Shapes("Image1").Visible

       

14  弹出和关闭窗体:

    UserForm_GOODS.Show     'UserForm_GOODS是窗体名称

    Unload UserForm_GOODS

       

15  设置登录窗口条件判断口令:

    If TextBox1.Value = "××" And TextBox2.Value = "×××" Then    '注：文本框2的显示属性设为密码﹡

       

16  用窗体添加列和输入文本的综合举例:

    Private Sub CommandButton1_Click()

    Dim irow As Integer

    irow = [a65536].End(xlUp).Row + 1

    Cells(irow, 1) = Me.ComboBox1.Value

    Cells(irow, 2) = Me.TextBox1.Value

    Cells(irow, 3) = Me.TextBox2.Value

    Me.ComboBox1.Value = ""

    Me.TextBox1.Value = ""

    Me.TextBox2.Value = ""

    End Sub

       

17  防止修改工作表名称:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Me.Name <> "VBA" Then Me.Name = "VBA"

    End Sub

       

18  设计让按钮跟随单元格选择而移动

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    With Me.CommandButton10

    .Top = Target.Top

    .Left = Target.Left + Target.Width

    End With

    End Sub

       

19  设置用户登陆窗口权限保护举例:

    Private Sub CommandButton1_Click()

    If TextBox1.Value = "张三" Or TextBox1.Value = "李四" Or TextBox1.Value = "王五" _

       Or TextBox1.Value = "赵六" Or TextBox1.Value = "孙七" Then

         If TextBox2.Value = "123456" Then

         Unload GOODS_系统登陆窗口

         Sheets("首页").Select

    Else

    End If

    End Sub

       

20  关闭全部工作表:

    Application.Quit

    ThisWorkbook.Close savechanges:=False

       

21  关闭当前活动工作表

    ThisWorkbook.Close savechanges:=False

       

22  显示及隐藏工作表

    Sheets("首页").Visible = xlSheetVisible

    Sheets("首页").Visible = xlSheetHidden

       

23  选择单元格:

    Range("C2").Select

    Range("C2,C5").Select

       

24  选择工作表:

    Sheets("首页").Select

       

25  选择并清空单元格内容:

    Range("C2,C3").Select

    Selection.ClearContents

       

26  设置列宽:

    Columns("B:B").ColumnWidth = 11.75

       

27  设置行高:

    Columns("1:1").RowHight = 11.75

       

28  设置单元格比较判断:

    If Range("B4").Value > Range("C4").Value Then

       

29  为单元格输入内容:

    Range("D4").Select

    ActiveCell.FormulaR1C1 = "危险"

       

30  设置单元格格式

    "Range(""A1:E1"").Select

    With Selection.Font

        .Name = ""宋体""

        .Size = 14

       

31  比较单元格数值:

    If Range("B4").Value > Range("C4").Value Then

       

32  引用单元格数值及举例

    Application.Range("D3").Value   '提取单元格D3的数值

    Sheets(Application.Range("D3").Value).Visible = xlSheetVisible   '显示以单元格D3的内容为名称的表格

       

33  设置标签从其他工作表的单元格取值

    Label6.Caption = Sheets("参数设置").Range("G5").Value & " " & " " & Sheets("参数设置").Range("C5")

       

34  从未打开的工作簿中取值:

    Sub GetValue()

    [A3] = "='E:\Goods\[VBA.xls]sheet1'!$B$10 "

    End Sub

       

35  删除空白单元格所在的行:

    Sub DeleteBalankRows()

    Range("A2:A10").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

    End Sub

       

36  让所有表格执行相同命令

    Sub 循环执行语句()

    Dim ws As Worksheet

    For Each ws In Worksheets

    ws.Activate

    命令内容？

    Next

    End Sub