VBA_事件01

'选区发生变化就调用某个方法--事件
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call biaoshi
End Sub
 

'with的简单使用
Sub withyuju()
With Sheet2
    .Range("A1") = 5
    .Range("A2") = 6
    .Range("A3") = 7    
End With
End Sub

 

'鼠标点击某行标识颜色
Sub biaoshi()
Dim colors As Long
colors = Application.RandBetween(1, 99999)
Cells.Interior.Pattern = xlNone '清除所有表的填充色
Selection.EntireRow.Interior.color = colors '标识颜色
End Sub

'单元格值发生变化-事件
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False '关闭事件响应
Call shaixuanshijian
Application.EnableEvents = True  '打开事件响应
End Sub

Sub shaixuanshijian()
Dim irow As Integer
irow = Range("a56433").End(xlUp).Row
Range("L1:q" & irow).ClearContents
Range("A1:F" & irow).AutoFilter Field:=4, Criteria1:=Range("i2")
Range("A1:F" & irow).Copy Range("k1")
Range("A1:F" & irow).AutoFilter
End Sub

'点击工作表-事件
Private Sub Worksheet_Activate()
Call gengxin
End Sub

Sub gengxin()
ActiveWorkbook.RefreshAll '更新数据
End Sub
 

'工作薄保存前-事件
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Call fuzhibaocunbook
End Sub

'复制表
Sub fuzhibaocunbook()
ThisWorkbook.SaveCopyAs "D:\VBA\备份" & Format(Now(), "yyyymmddhhmmss") & ".xlsx"  '设置当期日期格式
End Sub