《VBA常用代码-EXCELWord版.doc》由会员分享，可在线阅读，更多相关《VBA常用代码-EXCELWord版.doc（5页珍藏版）》请在三一文库上搜索。
1、传播优秀Word版文档 ，希望对您有帮助，可双击去除！EXCEL VBA常用代码1.显示活动工作簿名称MsgBox 当前活动工作簿是 & ActiveWorkbook.Name2.保存活动工作簿Activeworkbook.Save3.保存所有打开的工作簿关闭EXCELFor Each W in Application.WorkbooksW.SaveNext WApplication.Quit4.将网格线设置为蓝色ActiveWindow.GridlineColorIndex = 55.将工作表sheet1隐藏Sheet1.Visible = xlSheetVeryHidden6.将工作表Sh
2、tte1显示Sheet1.Visible = xlSheetVisible7.单击某单元格,该单元格所在的行以蓝色背景填充,字体颜色为白色Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If Target.Row = 2 Then第二行以下的区域 On Error Resume Next ChangColor_With1.FormatConditions.Delete Target.EntireRow.Name = ChangColor_With1 With ChangColor_With1.FormatCo
3、nditions .Delete .Add xlExpression, , TRUE .Item(1).Interior.ColorIndex = 5 .Item(1).Font.ColorIndex = 2 End With End IfEnd Sub8.使窗体在启动的时候自动最大化Private Sub UserForm_Initialize() Application.WindowState = xlMaximized With Application Me.Top = .Top传播优秀Word版文档 ，希望对您有帮助，可双击去除！ Me.Left = .Left Me.Height =
4、 .Height Me.Width = .Width End WithEnd Sub9.不保存工作簿退出EXCELApplication.DisplayAlerts = FalseApplication.Quit10.使窗体的关闭按纽不好用Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)If CloseMode = vbformcontrdmenu ThenMsgBox 请用关闭按钮关闭窗口!, 64, 提示Cancel = TrueEnd IfEnd Sub11.使窗体在3秒后自动关闭Privat
5、e Sub UserForm_Activate()Application.Wait 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在窗体上以隐藏方式存在
6、CommandButton10.Visible = 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(工资)
7、.Cells(X, 2)在Label2中显示Textbox1数据所在的第二列的数据 Label7.Caption = Sheets(工资).Cells(X, 3) 在Label2中显示Textbox1数据所在的第三列的数据 End If NextEnd Sub17.使EXCEL启动的时候自动最小化/最大化Private Sub Workbook_Open()Application.WindowState = xlMinimized最小化Application.WindowState = xlMaximized最大化End Sub传播优秀Word版文档 ，希望对您有帮助，可双击去除！18.在La
8、bel25以数字的形式显示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 lpCla
9、ssName 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 dwNe
10、wLong 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 Long.Public Property Set Form(oForm As Object) 29 If Val(Application.Version) = 2 Then第二行以下的所有列 On Error
11、 Resume Next ChangColor_With2.FormatConditions.Delete ChangColor_With3.FormatConditions.Delete Target.EntireRow.Name = ChangColor_With2 Target.EntireColumn.Name = ChangColor_With3 With ChangColor_With2.FormatConditions .Delete .Add xlExpression, , TRUE .Item(1).Interior.ColorIndex = 5 End With With
12、ChangColor_With3.FormatConditions .Delete .Add xlExpression, , TRUE .Item(1).Interior.ColorIndex = 5 End With End IfEnd Sub23.显示动态时间（1）插入窗体Userform1及Label1并在窗体声明中插入Option ExplicitPublic nextRun As Date（2）在窗体Activate事件中插入Showtime（3）在窗体QueryClose事件中插入Application.OnTime nextRun, showtime, schedule:=Fal
13、se（4）插入模块Module1并输入 Option ExplicitSub showtime()UserForm1.Label1 = NowUserForm1.RepaintDoEventsUserForm1.nextRun = Now + 1 / 86400Application.OnTime UserForm1.nextRun, showtime传播优秀Word版文档 ，希望对您有帮助，可双击去除！End Sub24.加载Combobox1选项ComboBox1.AddItem 收入型ComboBox1.Additem “支出型”25.使Textbox1自动程输入状态显示(有光标闪动)TextBox1.SetFocus26.打开C盘目录Shell explorer.exe C:, 127.