自学资料（Excel VBA）[收集整理16]

默认分类   2009-08-01 12:17   阅读58   评论0
字号： 大  中 小小
123、设置日历控件字号
Private Sub UserForm_Initialize()
Me.Calendar1.GridFont.Size = 14
End Sub
124、以下是从金刚金作品里提出来的部份事件代码，做得很好，值得借鉴学习：
Private Sub Workbook_Activate()
MsgBox "工作簿被切换为作用工作簿", vbInformation, "Workbook_Activate"
End Sub
Private Sub Workbook_AddinInstall()
MsgBox "激活新的加载宏时", vbInformation, "Workbook_AddinInstall"
End Sub
Private Sub Workbook_AddinUninstall()
MsgBox "取消以前选取的加载宏时", vbInformation, "Workbook_AddinUninstall"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "工作簿被关闭之前", vbInformation, "Workbook_BeforeClose"
End Sub
Private Sub Workbook_BeforePrint(Cancel As Boolean)
MsgBox "工作簿打印之前", vbInformation, "Workbook_BeforePrint"
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MsgBox "工作簿进行保存之前", vbInformation, "Workbook_BeforeSave"
End Sub
Private Sub Workbook_Deactivate()
MsgBox "工作簿切换为非作用工作簿", vbInformation, "Workbook_Deactivate"
End Sub
Private Sub Workbook_NewSheet(ByVal Sh As Object)
MsgBox "新建工作表", vbInformation, "Workbook_NewSheet"
End Sub
Private Sub Workbook_Open()
MsgBox "打开工作簿", vbInformation, "Workbook_Open"
End Sub
Private Sub Workbook_PivotTableCloseConnection(ByVal Target As PivotTable)
MsgBox "数据透视表关闭与其数据源的连接之后", vbInformation, "Workbook_PivotTableCloseConnection"
End Sub
Private Sub Workbook_PivotTableOpenConnection(ByVal Target As PivotTable)
MsgBox "数据透视表打开与其数据源的连接之后", vbInformation, "Workbook_PivotTableOpenConnection"
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
MsgBox "工作表" & Sh.Name & "切换为作用工作表", vbInformation, "Workbook_SheetActivate"
End Sub
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
MsgBox "任一单元格双击鼠标之后", vbInformation, "Workbook_SheetBeforeDoubleClick"
End Sub
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
MsgBox "任一单元格单击鼠标右键之后", vbInformation, "Workbook_SheetBeforeRightClick"
End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
MsgBox "工作表内容进行重算之后", vbInformation, "Workbook_SheetCalculate"
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox "在工作表内进行不同的操作", vbInformation, "Workbook_SheetChange"
End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
MsgBox "工作表" & Sh.Name & "切换为非作用工作表", vbInformation, "Workbook_SheetDeactivate"
End Sub
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
MsgBox "按下Excel超链接之后", vbInformation, "Workbook_SheetFollowHyperlink"
End Sub
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
MsgBox "数据透视表更新之后", vbInformation, "Workbook_SheetPivotTableUpdate"
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox "工作表里选取不同单元格范围时", vbInformation, "Workbook_SheetSelectionChange"
End Sub
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
MsgBox "工作簿切换为作用工作簿", vbInformation, "Workbook_WindowActivate"
End Sub
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
MsgBox "工作簿切换为非作用工作簿", vbInformation, "Workbook_WindowDeactivate"
End Sub
Private Sub Workbook_WindowResize(ByVal Wn As Window)
MsgBox "工作簿被打开或窗口最大化、最小化之后", vbInformation, "Workbook_WindowResize"
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "选取不同单元格范围时", vbInformation, "Worksheet_SelectionChange"
End Sub
125、Rem 选定单元格的个数
Sub t()
MsgBox Selection.Cells.Count
End Sub
126、Application.OnKey "{F11}", "ccc"禁用F11
127、'选中A列除A1外的第一个非空单元格
[a65536].End(xlUp).Offset(1, 0).Select
128、Private Sub CommandButton1_Click()
On Error GoTo ad '注意后面的ad，这是错误处理的用法
i = Range("a1").Value
Sheets(CStr(i)).Select
ret = MsgBox("是否覆盖", vbYesNo, "")
If ret = vbYes Then
Range("A5:C7").Copy Sheets(CStr(i)).Range("A1") '重点在copy之后
Else
Sheets.Add.Name = i & ".2"
Range("A5:C7").Copy Sheets(CStr(i & ".2")).Range("A1")
End If
End
ad:
Sheets.Add.Name = i '工作表名的变化是可见的
Range("A5:C7").Copy Sheets(CStr(i)).Range("A1")
End Sub
129、用变量取代Sheet1中的“1”
For i = 1 To 3
Sheets("sheet" & i).Range("A2") = "1234"
Next
转载自http://bbs.it.com.cn/showtopic-169943.aspx