Consulting

Results 1 to 3 of 3

Thread: Solved: Worksheet_Change not running right

  1. #1

    Question Solved: Worksheet_Change not running right

    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.)

    [VBA]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[/VBA]

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    [vba]

    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
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    D'oh - ok dumb question...thanks XLD, that fixed it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •