Worksheet Calculate

Worksheet_Calculate occurs after a sheet is recalculated.

The following sample compares a month's profits between the previous and the current year. If profit has fallen, a red down arrow appears below the month; if profit has risen, a green up arrow appears (see Figure 8.5):

Private Sub Worksheet_Calculate() Select Case Range("C3").Value Case Is > Range("C4").Value

SetArrow 10, msoShapeDownArrow Case Is < Range("C4").Value

SetArrow 3, msoShapeUpArrow End Select End Sub

Private Sub SetArrow(ByVal ArrowColor As Integer, ByVal ArrowDegree) ActiveSheet.Shapes.AddShape(ArrowDegree, 17.25, 43.5, 5, 10).Select With Selection.ShapeRange With .Fill .Visible = msoTrue .Solid

.ForeColor.SchemeColor = ArrowColor .Transparency = 0# End With With .Line .Weight = 0.75 .DashStyle = msoLineSolid .Style = msoLineSingle .Transparency = 0# .Visible = msoTrue .ForeColor.SchemeColor = 64 .BackColor.RGB = RGB(255, 255, 255) End With End With End Sub

0 0

Post a comment