Excel宏中常用的VBA 代码(2008-12-21 20:28:29)转载标签： excel应用宏vb杂谈 分类： Excel应用
1.显示活动工作簿名称
MsgBox "当前活动工作簿是" & https://www.doczj.com/doc/e915157292.html,
2.保存活动工作簿
Activeworkbook.Save
3.保存所有打开的工作簿关闭EXCEL
For Each W in Application.Workbooks
W.Save
Next W
Application.Quit
4.将网格线设置为蓝色
ActiveWindow.GridlineColorIndex = 5
5.将工作表sheet1隐藏
Sheet1.Visible = xlSheetVeryHidden
6.将工作表Shtte1显示
Sheet1.Visible = xlSheetVisible
7.单击某单元格,该单元格所在的行以蓝色背景填充,字体颜色为白色
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Row >= 2 Then’第二行以下的区域
On Error Resume Next
[ChangColor_With1].FormatConditions.Delete
https://www.doczj.com/doc/e915157292.html, = "ChangColor_With1"
With [ChangColor_With1].FormatConditions
.Delete
.Add xlExpression, , "TRUE"
.Item(1).Interior.ColorIndex = 5
.Item(1).Font.ColorIndex = 2
End With
End If
End Sub
8.使窗体在启动的时候自动最大化
Private Sub UserForm_Initialize()
Application.WindowState = xlMaximized
With Application
Me.Top = .Top
Me.Left = .Left
Me.Height = .Height
Me.Width = .Width
End With
End Sub
9.不保存工作簿退出EXCEL
Application.DisplayAlerts = False
Application.Quit
10.使窗体的关闭按纽不好用
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbformcontrdmenu Then
MsgBox "请用关闭按钮关闭窗口!!", 64, "提示"
Cancel = True
End If
End Sub
11.使窗体在3秒后自动关闭
Private Sub UserForm_Activate()
Application.Wait Now + TimeValue("00:00:03")
UserForm1.Hide
End Sub
12.启动窗体的时候自动使Label1显示Sheet1工作表3列,8行的内容
Private Sub UserForm_Activate()
Label1.Caption = Sheets("sheet1").Cells(3, 8)
End Sub
13.让按纽CommandButton1在窗体上以不可用状态显示
CommandButton1.Enabled = False
14.让按纽Commandbutton1在窗体上以隐藏方式存在
CommandButton10.Visible = False
15.点击Commandbutton1按纽进入”工资”工作表
Sheets("工资").Select
16.在Textbox1中输入数据,窗体可显示出”工资”工作表中与输入内容关联的项
Private Sub TextBox1_Change()
For X = 1 To Application.CountA(Sheets("工资").Range("a:a"))
If Sheets("工资").Cells(X, 1) = TextBox1.Text Then’在工资表第一列查找与Textbox1输入相符的项
Label2.Caption = Sheets("工资").Cells(X, 2)’在Label2中显示Textbox1数据所在的第二列的数据
Label7.Caption = Sheets("工资").Cells(X, 3) ’在Label2中显示Textbox1数据所在的第三列
的数据 End If
Next
End Sub
17.使EXCEL启动的时候自动最小化/最大化
Private Sub Workbook_Open()
Application.WindowState = xlMinimized’最小化
Application.WindowState = xlMaximized’最大化
End Sub
18.在Label25以数字的形式显示TextBox12×Label14的结果
Label25.Caption = Val(TextBox12.Text) * Val(Label14.Caption)
19.单选按纽名与Sheet6工作表名相同
OptionButton6.Caption = https://www.doczj.com/doc/e915157292.html,
20.”登陆”窗体的显示,隐藏
登陆.Show’显示
登陆.Hide’隐藏
21.使窗体的标题栏不显示
(1)插入类模块” CFormChanger” 代码如下:
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long
Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
Private Declare Function DrawMenuBar Lib "user32" (ByVal hWnd As Long) As Long
Private Const GWL_STYLE As Long = (-16)
Private Const WS_CAPTION As Long = &HC00000
Dim hWndForm As Long
……………………………………………………………………………………………………………..
Public Property Set Form(oForm As Object) '29
If Val(Application.Version) < 9 Then
hWndForm = FindWindow("ThunderXFrame", oForm.Caption)
Else
hWndForm = FindWindow("ThunderDFrame", oForm.Caption)
End If
SetFormStyle
End Property
……………………………………………………………………………………………………………….
Private Sub SetFormStyle()
Dim iStyle As Long, hMenu As Long, hID As Long, iItems As Integer
iStyle = GetWindowLong(hWndForm, GWL_STYLE)
iStyle = iStyle And Not WS_CAPTION
iStyle = iStyle Or WS_THICKFRAME
SetWindowLong hWndForm, GWL_STYLE, iStyle
DrawMenuBar hWndForm
End Sub
(2)在所在窗体代码里声明
Dim oFormChanger As New CFormChanger
(3).在窗体的Activate事件中插入代码
Set oFormChanger.Form = Me
Me.SpecialEffect = fmspecia1EffectRaised
以上三步每一步都不可缺少,否则不能完成.
22.单击某单元格,该单元格所在的行与列都以蓝色背景填充
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Row >= 2 Then’第二行以下的所有列
On Error Resume Next
[ChangColor_With2].FormatConditions.Delete
[ChangColor_With3].FormatConditions.Delete
https://www.doczj.com/doc/e915157292.html, = "ChangColor_With2"
https://www.doczj.com/doc/e915157292.html, = "ChangColor_With3"
With [ChangColor_With2].FormatConditions
.Delete
.Add xlExpression, , "TRUE"
.Item(1).Interior.ColorIndex = 5
End With
With [ChangColor_With3].FormatConditions
.Delete
.Add xlExpression, , "TRUE"
.Item(1).Interior.ColorIndex = 5
End With
End If
End Sub
23.显示动态时间
（1）插入窗体Userform1及Label1并在窗体声明中插入
Option Explicit
Public nextRun As Date
（2）在窗体Activate事件中插入
Showtime
（3）在窗体QueryClose事件中插入
Application.OnTime nextRun, "showtime", schedule:=False
（4）插入模块Module1并输入
Option Explicit
Sub showtime()
https://www.doczj.com/doc/e915157292.html,bel1 = Now
UserForm1.Repaint
DoEvents
UserForm1.nextRun = Now + 1 / 86400
Application.OnTime UserForm1.nextRun, "showtime"
End Sub
24.加载Combobox1选项
ComboBox1.AddItem "收入型"
ComboBox1.Additem “支出型”
25.使Textbox1自动程输入状态显示(有光标闪动)
TextBox1.SetFocus
26.打开C盘目录
Shell "explorer.exe C:\", 1
27、工作表保护、取消
取消保护：ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False, Password:="123"
添加保护：ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="123"