Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,421
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Macro to warn if Bal > or less than 0 (2007)

    I have the following macro loaded in the worksheet module.

    I need the macro amended so that when Bal in P2 is greater than zero or less than zero
    a message must come up advising that there is a balance in P2.
    At the moment one needs to click on cell P2 before message appears

    Your assistance in this regard will be most appreciated

    Regards

    Howard

    Private Sub Worksheet_Change(ByVal Target As Range)
    Const WS_RANGE As String = "P2" '<== change to suit

    On Error GoTo ws_exit
    Application.EnableEvents = False

    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
    With Target

    If .Value <> 0 Then

    MsgBox "P2 not zero"
    End If
    End With
    End If

    ws_exit:
    Application.EnableEvents = True
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to warn if Bal > or less than 0 (2007)

    Does P2 contain a formula? The Worksheet_Change event does *not* occur when the value of a cell changes as the result of a formula, only when the user edits it.

    One workaround is to inspect all cells that contribute to the result of the formula, but this could get hairy if there are many such cells.

    Another is to use the Worksheet_Calculate event. This will occur whenever the formula is recalculated. The disadvantage is that it occurs whenever *any* formula is recalculated - you can't filter on the target, as in Worksheet_Change. So it might have a negative impact on performance.

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Macro to warn if Bal > or less than 0 (2007)

    Hi Howard,

    Try replacing your present code with:

    Option Explicit
    Const WS_RANGE As String = "P2" '<== change to suit

    Private Sub Worksheet_Calculate()
    If Me.Range(WS_RANGE).Value <> 0 Then MsgBox WS_RANGE & " not zero"
    End Sub

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then _
    If Me.Range(WS_RANGE).Value <> 0 Then MsgBox WS_RANGE & " not zero"
    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then _
    If Target.Value <> 0 Then MsgBox WS_RANGE & " not zero"
    End Sub

    Note: The above routines capture the present value changes plus any selection changes and worksheet recalculations. As coded, you'll get two prompts if the worksheet recalculates.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,421
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Macro to warn if Bal > or less than 0 (2007)

    Hi Paul

    Thanks for the help

    Code works perfectly

    Regards

    Howard

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,421
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Macro to warn if Bal > or less than 0 (2007)

    Hi Paul

    Would it be possible to amend your macro, so that the message only appears once when there is a balance in cell P2

    Your assistance in this regard will be most appreciated

    Regards

    Howard
    .

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Macro to warn if Bal > or less than 0 (2007)

    Hi Howard,

    Try the following:

    Option Explicit
    Const WS_RANGE As String = "P2" '<== change to suit
    Dim Dirty As Boolean

    Private Sub Worksheet_Calculate()
    If Me.Range(WS_RANGE).Value <> 0 Then MsgBox WS_RANGE & " not zero"
    Dirty = True
    End Sub

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Dirty = True Then
    Dirty = False
    Exit Sub
    End If
    If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then _
    If Me.Range(WS_RANGE).Value <> 0 Then MsgBox WS_RANGE & " not zero"
    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Dirty = True Then
    Dirty = False
    Exit Sub
    End If
    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then _
    If Target.Value <> 0 Then MsgBox WS_RANGE & " not zero"
    End Sub

    Now you should only get two prompts if you change the value in P2 to a non-sero value and select another cell in the same operation.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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