PDA

View Full Version : Solved: Worksheet_Change not running right



Gingertrees
09-09-2010, 10:27 AM
Cell C1 has a formula in it, e.g. "=B1*2" I want this worksheet_change module to show a msgbox if the value of C1 is greater than 4.
The following code works if I select a cell with no formula and just type "5" or "11" etc. How do I get it to work with C1, despite the fact that C1 has a formula in it? (Note, C1 currently formatted as a percentage cell, but the same problem arises when it's just a number cell too.)

Private Sub Worksheet_Change(ByVal Target As Range)
'Do nothing if more than one cell is changed or content deleted
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$C$1" Then
If Target.Value > 4 Then
MsgBox ("Are you sure? Over 400%")
End If

On Error GoTo 0
End If
End If

End Sub

xld
09-09-2010, 11:05 AM
Private Sub Worksheet_Calculate()
With Me.Range("C1")
If Not IsError(.Value) Then

If .Value > 4 Then MsgBox "Are you sure? Over 400%"
End If
End With
End Sub

Gingertrees
09-09-2010, 11:13 AM
D'oh - ok dumb question...thanks XLD, that fixed it. :)