Microsoft Office Tutorials and References
In Depth Information
Workbook_SheetActivate Event
Workbook_sheetActivate Event
The Workbook_SheetActivate event is triggered when a sheet is activated in the workbook.
Suppose you want to always return to cell A1 whenever you activate any worksheet, regardless of
what cell you had selected the last time you were in that worksheet. The following procedure using
the Application.GoTo statement does just that:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If TypeName(Sh) = “Worksheet” Then Application.Goto Range(“A1”), True
End Sub
This example illustrates the distinction between a Sheet object and a Worksheet
object — they are not necessarily the same things. Excel has several types of Sheet
objects: Worksheets, Chart sheets, Dialog sheets, and the obsolete Macro sheets. In
this example, a Chart sheet would create confusion for VBA because Chart sheets
do not contain cells. Only worksheets contain cells, which is why the TypeName
of Worksheet is the only Sheet object at which this procedure’s code is directed.
Workbook_sheetdeactivate Event
The Workbook_SheetDeactivate event is triggered when a sheet loses focus, such as when a
different sheet in the workbook is activated. If you have a workbook with tables of data on every
worksheet, and you want the tables to be sorted automatically by column A whenever you leave
the worksheet, this procedure does that:
Private Sub Workbook_SheetDeActivate(ByVal Sh As Object)
If TypeName(Sh) = “Worksheet” Then
Sh.Range(“A1”).CurrentRegion.Sort Key1:=Sh.Range(“A2”), _
Order1:=xlAscending, Header:=xlYes
End If
End Sub
Workbook_Beforesave Event
The Workbook_BeforeSave event is triggered just before the workbook is saved. You can set the
Cancel parameter to True to stop the workbook from being saved.
Suppose you want to limit the time period for a workbook to be saved. The following procedure
allows the workbook to be saved only between 9:00 AM and 5:00 PM:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If VBA.Time < TimeValue(“09:00”) _
Or VBA.Time > TimeValue(“17:00”) Then Cancel = True
End Sub
Search JabSto ::

Custom Search