Worksheet Calculate

The Worksheet_Calculate event occurs whenever the worksheet is recalculated. It is usually triggered when you enter new data into cells that are referenced in formulas in the worksheet. You could use the Worksheet_Calculate event to warn you, as you enter new data assumptions into a forecast, when key results go outside their expected range of values. In the Figure 9-1 worksheet, you want to know when the profit figure in cell N9 exceeds 600 or is lower than 500.

Cirfts. -I7IT1

-

— T

A 1

B

C

D

E

F

G

fi •

1

J

K L

M

N

1 I

Walrus Enterprises

r

2 Jan Sales ^

951

Microsoft Excel

P

H

3 Growth/Mo

0.50%

o

5 ~

63%

Profit has

fallen to 451 3

6 :

Feb

100.5

101 0

Jul

7 Sales

99 0

99.5

100.0

101.5

102.0

1 OK

04 1

104.S

1221.2

& : Costs

62.4

62 7

63 0

63 3

63 6

63 9

64 3

65 6

65 9

7694

9 Profit

36 6

36.8

37.0

37,2

37 4

37 6

37 7

37 9

38 1

35.3

38 E

38 7

451.9

10.

h < • Eham

Profit

Afra^sis

J"

I'HI

Figure 9-1

The following event procedure runs every time the worksheet recalculates, checks cell N9, which has been named FinalProfit, and generates messages if the figure goes outside the required band of values:

Private Sub Worksheet_Calculate() Dim dProfit As Double

'After recalc access value in FinalProfit cell

dProfit = Me.Range("FinalProfit'

').Value

'Display value if outside range

500 to 600

If dProfit > 600 Then

MsgBox "Profit has risen to 1

' & Format(dProfit,

"#,##0.0")

, vbExclamation

Elself dProfit < 500 Then

MsgBox "Profit has fallen to

" & Format(dProfit,

"#,##0.0"

), vbCritical

End If

End Sub

0 0

Post a comment