办公高手.EXCEL常用宏命令1 设置打开时弹出对话框的命令举例:Sub AUTO_OPEN()"输入打开工作表时要运行的代码或宏"End Bub2 设置关闭时弹出对话框的命令举例:Sub AUTO_CLOSE()"输入关闭工作表时要运行的代码或宏"End Bub3 设置提示框举例:MsgBox prompt:="温馨提示：您要打开的工作表有保护！", Buttons:=vbOKOnly, Title:="谷子提示"i = MsgBox("系统提供不同服务，是否浏览资费信息？", vbYesNoCancel, "谷子提示：")If i = vbYes ThenMsgBox "资费信息：********", , "谷子提示"Else"输入要运行的代码"End If4、提示框内容过长，换行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" ThenMsgBox "对不起，测试期间已经结束", , "谷子提示"ElseSheets("首页").Select '或其他命令End IfEnd Sub7 禁止保存或另存的命令:Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)MsgBox ("禁止保存")Cancel = True '或thisworkbook.saved=trueEnd Sub8 保护工作表与撤销保护Sheet2.Protect Password:="123456", DrawingObjects:=True, Contents:=True, Scenarios:=True '保护工作表SHEET2Sheet2.Unprotect ("123456") '解除保护9 保护工作簿与撤销保护ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="123"ActiveWorkbook.Unprotect Password:="123"10 保护全部工作表Sub 保护所有工作表()Dim ws As WorksheetFor Each ws In Worksheetsws.ActivateActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="123"NextEnd Sub11 抽取各表名称Sub 抽取各表名称()Range("a1").Value = n = ThisWorkbook.Sheets.CountRange("a2").Value = nFor i = 1 To nCells(i, 2).Value = Sheets(i).NameNextEnd SubEXCEL2007命令Dim n As Variant '指定变量Dim i As Integer '指定变量Range("a1").Value = n = ThisWorkbook.Sheets.CountRange("a2").Value = n For i = 1 To nCells(i, 2).Value = Sheets(i).NameNext12、显示/隐藏图表的命令ActiveSheet.ChartObjects("图表 1").Visible = Not ActiveSheet.ChartObjects("图表1").Visible13、显示/隐藏图片的命令ActiveSheet.Shapes("Image1").Visible = True ActiveSheet.Shapes("Image1").Visible = FalseActiveSheet.Shapes("Image1").Visible = Not ActiveSheet.Shapes("Image1").Visible14 弹出和关闭窗体:UserForm_GOODS.Show 'UserForm_GOODS是窗体名称Unload UserForm_GOODS15 设置登录窗口条件判断口令:If TextBox1.Val ue = "××" And TextBox2.Value = "×××" Then '注：文本框2的显示属性设为密码﹡16 用窗体添加列和输入文本的综合举例:Private Sub CommandButton1_Click()Dim irow As Integerirow = [a65536].End(xlUp).Row + 1Cells(irow, 1) = boBox1.ValueCells(irow, 2) = Me.TextBox1.ValueCells(irow, 3) = Me.TextBox2.ValueboBox1.Value = ""Me.TextBox1.Value = ""Me.TextBox2.Value = ""End Sub17 防止修改工作表名称:Private Sub Worksheet_SelectionChange(ByVal Target As Range)If <> "VBA" Then = "VBA"End Sub18 设计让按钮跟随单元格选择而移动Private Sub Worksheet_SelectionChange(ByVal Target As Range)With mandButton10.Top = Target.Top.Left = Target.Left + Target.WidthEnd WithEnd Sub19 设置用户登陆窗口权限保护举例:Private Sub CommandButton1_Click()If TextBox1.Value = "张三" Or TextBox1.Value = "李四" Or TextBox1.Value = "王五" _Or TextBox1.Value = "赵六" OrTextBox1.Value = "孙七" ThenIf TextBox2.Value = "123456" ThenUnload GOODS_系统登陆窗口Sheets("首页").SelectElseEnd If End Sub20 关闭全部工作表:Application.QuitThisWorkbook.Close savechanges:=False21 关闭当前活动工作表ThisWorkbook.Close savechanges:=False22 显示及隐藏工作表Sheets("首页").Visible = xlSheetVisibleSheets("首页").Visible =xlSheetHidden23 选择单元格:Range("C2").SelectRange("C2,C5").Select24 选择工作表:Sheets("首页").Select25 选择并清空单元格内容:Range("C2,C3").SelectSelection.ClearContents26 设置列宽:Columns("B:B").ColumnWidth = 11.7527 设置行高:Columns("1:1").RowHight = 11.7528 设置单元格比较判断:If Range("B4").Value > Range("C4").Value Then29 为单元格输入内容:Range("D4").SelectActiveCell.FormulaR1C1 = "危险"30 设置单元格格式"Range(""A1:E1"").SelectWith Selection.Font.Name = ""宋体"".Size = 1431 比较单元格数值:If Range("B4").Value > Range("C4").Value Then32 引用单元格数值及举例Application.Range("D3").Value '提取单元格D3的数值Sheets(Application.Range("D3").Value).Visib le = xlSheetVisible '显示以单元格D3的内容为名称的表格33 设置标签从其他工作表的单元格取值Label6.Caption = Sheets("参数设置").Range("G5").Value & " " & " " & Sheets("参数设置").Range("C5")34 从未打开的工作簿中取值:Sub GetValue()[A3] = "='E:\Goods\[VBA.xls]sheet1'!$B$10 "End Sub35 删除空白单元格所在的行:Sub DeleteBalankRows()Range("A2:A10").SpecialCells(xlCellTypeBlan ks).EntireRow.DeleteEnd Sub 36 让所有表格执行相同命令Sub 循环执行语句()Dim ws As WorksheetFor Each ws In Worksheets ws.Activate命令内容？NextEnd Sub。