办公高手 EXCEL常用宏命令

办公高手.EXCEL常用宏命令

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

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

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

\输入关闭工作表时要运行的代码或宏\ End Bub 3 设置提示框举例:

MsgBox prompt:=\温馨提示：您要打开的工作表有保护！\谷子提示\

i = MsgBox(\系统提供不同服务，是否浏览资费信息？\谷子提示：\ If i = vbYes Then

MsgBox \资费信息：********\谷子提示\ Else

\输入要运行的代码\ End If

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

MsgBox \您本次访问系统的时间是：\系统离到期日还剩余：\ & DateDiff(\Now, \& \天!\& vbNewLine & \是否查阅到期日说明？\vbYesNoCancel, \时间和到期日提示：\

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

MsgBox \系统当前日期和时间：\时间提示\ DateDiff(\天!\ ‘提示离指定日期的剩余天数 6 系统时间判断命令: Sub 判断时间测试() If Date > \

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

Sheets(\首页\ '或其他命令 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:=\DrawingObjects:=True, Contents:=True, Scenarios:=True 保护工作表SHEET2

Sheet2.Unprotect (\ '解除保护

9 保护工作簿与撤销保护

ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:=\

' ActiveWorkbook.Unprotect Password:=\ 10 保护全部工作表 Sub 保护所有工作表() Dim ws As Worksheet For Each ws In Worksheets ws.Activate

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=\ Next End Sub 11 抽取各表名称 Sub 抽取各表名称()

Range(\ n = ThisWorkbook.Sheets.Count Range(\ 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(\ n = ThisWorkbook.Sheets.Count

Range(\ For i = 1 To n

Cells(i, 2).Value = Sheets(i).Name Next 12、显示/隐藏图表的命令

ActiveSheet.ChartObjects(\图表 1\图表 1\ 13、显示/隐藏图片的命令

ActiveSheet.Shapes(\ ActiveSheet.Shapes(\

ActiveSheet.Shapes(\ 14 弹出和关闭窗体:

UserForm_GOODS.Show 'UserForm_GOODS是窗体名称 Unload UserForm_GOODS

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

If TextBox1.Value = \ '注：文本框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 <> \ 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 = \赵六\孙七\ If TextBox2.Value = \ Unload GOODS_系统登陆窗口