办公高手EXCEL常用宏命令

办公高手.EXCEL常用宏命令

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 = https://www.360docs.net/doc/6c15421262.html,

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 = https://www.360docs.net/doc/6c15421262.html,

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.Val ue = "××" And TextBox2.Value = "×××" Then '注：文本框2的显示属性设为密码﹡

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

Private Sub CommandButton1_Click()

Dim irow As Integer

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

Cells(irow, 1) = https://www.360docs.net/doc/6c15421262.html,boBox1.Value

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

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

https://www.360docs.net/doc/6c15421262.html,boBox1.Value = ""

Me.TextBox1.Value = ""

Me.TextBox2.Value = ""

End Sub

17 防止修改工作表名称:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If https://www.360docs.net/doc/6c15421262.html, <> "VBA" Then https://www.360docs.net/doc/6c15421262.html, = "VBA"

End Sub

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

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With https://www.360docs.net/doc/6c15421262.html,mandButton10

.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