《EXCELVBA常用代码集.docVIP

EXCEL VBA常用代码集 1.显示活动工作簿名称 MsgBox 当前活动工作簿是 ActiveWorkbook.Name 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 Target.EntireRow.Name = 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中显示Text