123、設(shè)置日歷控件字號
Private Sub UserForm_Initialize()
Me.Calendar1.GridFont.Size = 14
End Sub
124、以下是從金剛金作品里提出來的部份事件代碼，做得很好，值得借鑒學(xué)習(xí)：
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 "工作簿被關(guān)閉之前", 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 "工作簿進(jìn)行保存之前", 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 "數(shù)據(jù)透視表關(guān)閉與其數(shù)據(jù)源的連接之后", vbInformation, "Workbook_PivotTableCloseConnection"
End Sub
Private Sub Workbook_PivotTableOpenConnection(ByVal Target As PivotTable)
MsgBox "數(shù)據(jù)透視表打開與其數(shù)據(jù)源的連接之后", 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 "任一單元格雙擊鼠標(biāo)之后", vbInformation, "Workbook_SheetBeforeDoubleClick"
End Sub
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
MsgBox "任一單元格單擊鼠標(biāo)右鍵之后", vbInformation, "Workbook_SheetBeforeRightClick"
End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
MsgBox "工作表內(nèi)容進(jìn)行重算之后", vbInformation, "Workbook_SheetCalculate"
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox "在工作表內(nèi)進(jìn)行不同的操作", 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 "數(shù)據(jù)透視表更新之后", 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 選定單元格的個數(shù)
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") '重點(diǎn)在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

轉(zhuǎn)載自 http://bbs./showtopic-169943.aspx