爱华网1设置打开时弹出对话框的命令举例:
Sub AUTO_OPEN()
"输入打开工作表时要运行的代码或宏"
End Bub
2设置关闭时弹出对话框的命令举例:
Sub AUTO_CLOSE()
"输入关闭工作表时要运行的代码或宏"
End Bub
3设置提示框举例:
MsgBox prompt:="温馨提示：您要打开的工作表有保护！", Buttons:=vbOKOnly,Title:="谷子提示"
i= MsgBox("系统提供不同服务，是否浏览资费信息？", vbYesNoCancel, "谷子提示：")
Ifi = 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 判断时间测试()
IfDate > "2010-05-20" Then
MsgBox "对不起，测试期间已经结束", , "谷子提示"
Else
Sheets("首页").Select '或其他命令
End If
End Sub
7禁止保存或另存的命令:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, CancelAs 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 = NotActiveSheet.ChartObjects("图表 1").Visible
13、显示/隐藏图片的命令
ActiveSheet.Shapes("Image1").Visible = True
ActiveSheet.Shapes("Image1").Visible = False
ActiveSheet.Shapes("Image1").Visible = NotActiveSheet.Shapes("Image1").Visible
14弹出和关闭窗体:
UserForm_GOODS.Show'UserForm_GOODS是窗体名称
Unload UserForm_GOODS
15设置登录窗口条件判断口令:
IfTextBox1.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 AsRange)
IfMe.Name <> "VBA" Then Me.Name ="VBA"
End Sub
18设计让按钮跟随单元格选择而移动
Private Sub Worksheet_SelectionChange(ByVal Target AsRange)
With Me.CommandButton10
.Top = Target.Top
.Left = Target.Left + Target.Width
End With
End Sub
19设置用户登陆窗口权限保护举例:
Private Sub CommandButton1_Click()
IfTextBox1.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设置单元格比较判断:
IfRange("B4").Value > Range("C4").ValueThen
29为单元格输入内容:
Range("D4").Select
ActiveCell.FormulaR1C1 = "危险"
30设置单元格格式
"Range(""A1:E1"").Select
With Selection.Font
.Name = ""宋体""
.Size = 14
31比较单元格数值:
IfRange("B4").Value > Range("C4").ValueThen
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
