1、(第 1 页)EXCEL VBA 常用代码1.显示活动工作簿名称MsgBox “当前活动工作簿是 “ & ActiveWorkbook.Name2.保存活动工作簿Activeworkbook.Save3.保存所有打开的工作簿关闭 EXCELFor Each W in Application.WorkbooksW.SaveNext WApplication.Quit4.将网格线设置为蓝色ActiveWindow.GridlineColorIndex = 55.将工作表 sheet1 隐藏Sheet1.Visible = xlSheetVeryHidden6.将工作表 Shtte1 显示Sheet
2、1.Visible = xlSheetVisible7.单击某单元格,该单元格所在的行以蓝色背景填充,字体颜色为白色Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)If Target.Row = 2 Then第二行以下的区域On Error Resume NextChangColor_With1.FormatConditions.DeleteTarget.EntireRow.Name = “ChangColor_With1“With ChangColor_With1.FormatConditions.Delet
3、e.Add xlExpression, , “TRUE“.Item(1).Interior.ColorIndex = 5.Item(1).Font.ColorIndex = 2End WithEnd IfEnd Sub8.使窗体在启动的时候自动最大化Private Sub UserForm_Initialize()Application.WindowState = xlMaximizedWith ApplicationMe.Top = .Top(第 2 页)Me.Left = .LeftMe.Height = .HeightMe.Width = .WidthEnd WithEnd Sub9.不
4、保存工作簿退出 EXCELApplication.DisplayAlerts = FalseApplication.Quit10.使窗体的关闭按纽不好用Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)If CloseMode = vbformcontrdmenu ThenMsgBox “请用关闭按钮关闭窗口!“, 64, “提示“Cancel = TrueEnd IfEnd Sub11.使窗体在 3 秒后自动关闭Private Sub UserForm_Activate()Application.W
5、ait Now + TimeValue(“00:00:03“)UserForm1.HideEnd Sub12.启动窗体的时候自动使 Label1 显示 Sheet1 工作表 3 列 ,8 行的内容Private Sub UserForm_Activate()Label1.Caption = Sheets(“sheet1“).Cells(3, 8)End Sub13.让按纽 CommandButton1 在窗体上以不可用状态显示CommandButton1.Enabled = False14.让按纽 Commandbutton1 在窗体上以隐藏方式存在CommandButton10.Visibl
6、e = False15.点击 Commandbutton1 按纽进入 ”工资”工作表Sheets(“工资“).Select16.在 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(
7、X, 2) 在 Label2 中显示 Textbox1 数据所在的第二列的数据Label7.Caption = Sheets(“工资“).Cells(X, 3) 在 Label2 中显示 Textbox1 数据所在的第三列的数据 End IfNextEnd Sub17.使 EXCEL 启动的时候自动最小化/ 最大化Private Sub Workbook_Open()Application.WindowState = xlMinimized最小化Application.WindowState = xlMaximized最大化End Sub(第 3 页)18.在 Label25 以数字的形式显示
8、 TextBox12Label14 的结果Label25.Caption = Val(TextBox12.Text) * Val(Label14.Caption)19.单选按纽名与 Sheet6 工作表名相同OptionButton6.Caption = Sheet6.Name20.”登陆”窗体的显示,隐藏登陆.Show 显示登陆.Hide隐藏21.使窗体的标题栏不显示(1)插入类模块” CFormChanger” 代码如下:Private Declare Function FindWindow Lib “user32“ Alias “FindWindowA“ (ByVal lpClassNa
9、me As String, ByVal lpWindowName As String) As LongPrivate Declare Function GetWindowLong Lib “user32“ Alias “GetWindowLongA“ (ByVal hWnd As Long, ByVal nIndex As Long) As LongPrivate Declare Function SetWindowLong Lib “user32“ Alias “SetWindowLongA“ (ByVal hWnd As Long, ByVal nIndex As Long, ByVal
10、dwNewLong As Long) As LongPrivate Declare Function DrawMenuBar Lib “user32“ (ByVal hWnd As Long) As LongPrivate Const GWL_STYLE As Long = (-16)Private Const WS_CAPTION As Long = &HC00000Dim hWndForm As LongPublic Property Set Form(oForm As Object) 29If Val(Application.Version) = 2 Then第二行以下的所有列On Er
11、ror Resume NextChangColor_With2.FormatConditions.DeleteChangColor_With3.FormatConditions.DeleteTarget.EntireRow.Name = “ChangColor_With2“Target.EntireColumn.Name = “ChangColor_With3“With ChangColor_With2.FormatConditions.Delete.Add xlExpression, , “TRUE“.Item(1).Interior.ColorIndex = 5End WithWith C
12、hangColor_With3.FormatConditions.Delete.Add xlExpression, , “TRUE“.Item(1).Interior.ColorIndex = 5End WithEnd IfEnd Sub23.显示动态时间（1）插入窗体 Userform1 及 Label1 并在窗体声明中插入Option ExplicitPublic nextRun As Date（2）在窗体 Activate 事件中插入Showtime（3）在窗体 QueryClose 事件中插入Application.OnTime nextRun, “showtime“, schedul
13、e:=False（4）插入模块 Module1 并输入Option ExplicitSub showtime()UserForm1.Label1 = NowUserForm1.RepaintDoEventsUserForm1.nextRun = Now + 1 / 86400Application.OnTime UserForm1.nextRun, “showtime“(第 5 页)End Sub24.加载 Combobox1 选项ComboBox1.AddItem “收入型“ComboBox1.Additem “支出型 ”25.使 Textbox1 自动程输入状态显示( 有光标闪动)TextBox1.SetFocus26.打开 C 盘目录Shell “explorer.exe C:“, 127.
