Microsoft Office Tutorials and References
In Depth Information
The Workbook_Deactivate event is triggered when the workbook loses focus, such as when a
different Excel workbook is activated or when the workbook is closed. The following example prompts
a Message Box to alert you when the workbook is deactivated:
Private Sub Workbook_Deactivate()
MsgBox “You are leaving “ & Me.Name & “!!”, _
“Just so you know...”
The Workbook_SheetChange event is triggered when any cell’s contents are changed on any
worksheet in the workbook. If you would like to keep a log of the date, time, sheet name, and address of
any cell that gets changed, this procedure accomplishes that, by listing information on a worksheet
na med “L og”:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
‘The log sheet will hold the record of each sheet change,
‘so do not monitor the Log sheet.
If Sh.Name = “Log” Then Exit Sub
‘Declare a Long variable for the next available row on the Log sheet.
Dim NextRow As Long
‘Assign the row number to the next empty row below that last row of data
‘in column A.
NextRow = Worksheets(“Log”).Cells(Rows.Count, 1).End(xlUp).Row + 1
‘In column A, enter the date of the changed cell.
Worksheets(“Log”).Cells(NextRow, 1).Value = VBA.Date
‘In column B, enter the time of the changed cell.
Worksheets(“Log”).Cells(NextRow, 2).Value = VBA.Time
‘In column C, enter the name of the worksheet holding the changed cell.
Worksheets(“Log”).Cells(NextRow, 3).Value = Sh.Name
‘In column D, enter the address of the changed cell.
Worksheets(“Log”).Cells(NextRow, 4).Value = Target.Address
‘Autofit the columns on the Log sheet, to make the information readable.
The Workbook_SheetSelectionChange event is triggered when a different cell is selected on any
worksheet in the workbook. In Lesson 11, you saw an example of the Worksheet_SelectionChange
event whereby the active cell was continuously highlighted. If you are navigating through large
ranges of data on your worksheets, such as budgets or financial reports, you might find it useful to
visually identify more than just the active cell. The following procedure highlights the entire row
and column at each new cell selection:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
Dim myRow As Long, myColumn As Long
myRow = Target.Row