Microsoft Office Tutorials and References
In Depth Information
Worksheet_Deactivate Event
every time you go to that worksheet, you want to know that the pivot tables are all refreshed and up
to date. The following event code accomplishes that task:
Private Sub Worksheet_Activate()
Dim intCounter As Integer
For intCounter = 1 To ActiveSheet.PivotTables.Count
ActiveSheet.PivotTables(intCounter).PivotCache.Refresh
Next intCounter
End Sub
Worksheet_deactivate Event
The Worksheet_Deactivate event occurs when you activate a different worksheet than the one you
were on. Suppose there is a particular cell in a worksheet that you strongly prefer to have some value
entered into before the user exits that worksheet. The following Worksheet_Deactivate event code
checks to see if cell A1 contains a value. If it does not, a Message Box alerts the users as a reminder
of that fact when they deactivate the worksheet.
Private Sub Worksheet_Deactivate()
If Len(Me.Range(“A1”).Value) = 0 Then _
MsgBox “FYI and reminder: you did not enter a value in cell A1” & vbCrLf & _
“in the worksheet named “ & Me.Name & “.”, _
vbExclamation, _
“Cell A1 should have some value in it!”
End Sub
Worksheet_calculate Event
The Worksheet_Calculate event occurs when the worksheet is recalculated. Suppose you have a
budget model and you want to monitor the bottom-line number for profit and loss, which is derived
by a formula in cell Z135. You could conditionally format the cell when its returned value is outside
an acceptable range, but chances are no one will see the formatting due to the location of the cell.
To give the budget model’s bottom-line number a boost in awareness, utilize the Worksheet_
Calculate event to make a Message Box pop up as a warning when the number in cell Z135
becomes lower than $1,000. Also, to make it fun, have a congratulatory message appear if the
profit number is greater than or equal to $5,000.
Private Sub Worksheet_Calculate()
If Range(“Z135”).Value < 1000 Then
MsgBox “Profits are too low!!”, vbExclamation, “Warning!!”
ElseIf Range(“Z135”).Value >= 5000 Then
MsgBox “Profits are TERRIFIC!!”, vbExclamation, “Wow, good news!!”
End If
End Sub
Search JabSto ::




Custom Search