[中学]EXCEL常用宏命令

[中学]EXCEL常用宏命令 EXCEL常用宏命令 办公高手.EXCEL常用宏命令 (2010-12-02 20:56:02)转载? 标签: 财经 分类: EXCEL- ppt 关于艾滋病ppt课件精益管理ppt下载地图下载ppt可编辑假如ppt教学课件下载triz基础知识ppt -高级应用 1 设置打开时弹出对话框的命令举例: Sub AUTO_OPEN() "输入打开工作 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 时要运行的代码或宏" 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.De lete End Sub 36 让所有表格执行相同命令 Sub 循环执行语句() Dim ws As Worksheet For Each ws In Worksheets