Consulting

Results 1 to 8 of 8

Thread: Popup to display warning message

  1. #1
    VBAX Contributor
    Joined
    Jun 2019
    Posts
    155
    Location

    Popup to display warning message

    Hi,

    I am having data range of A5 : B104. The % variation between these two column A & B (for example A5 & B5 till A104 & B104) is updating on C5 to C104. While i am entering the data in "B5 to B104" if the value is crossing ±0.25 the popup message to be display ("Aggregate value is crossed the tolerance limit")

    And other range is A109 : B133. On this range if the value is crossing ±0.25 the popup message to be display ("Cement value is crossed the tolerance limit")

    But this popup to be display when the value is updating the specified range.

    can any one please help me for this.

    The sample file is updating here.
    Attached Files Attached Files

  2. #2
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not (Intersect(Target, Range("A5:C104")) Is Nothing) And Cells(Target.Row, 4) > 0.25 Then
            MsgBox "Aggregate value has crossed the tolerance limit"
        End If
    End Sub
    Semper in excretia sumus; solum profundum variat.

  3. #3
    VBAX Contributor
    Joined
    Jun 2019
    Posts
    155
    Location
    Dear Paulked

    Thaks for your reply. It is working grate.

    I have one more doubt. Is it possible to check the last updated row between the range of A5 : B104 and display the same message only for the last updated row.

    For example i am entering the data from A5 to A50 only. And the message should be display for A50 only
    And another time i entered from A5 to A75. then the message should be display for A75 only.

    Can you please help me for this

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Maybe


    Option Explicit
    
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim r As Range, r1 As Range
        
        Set r = Target.Cells(1, 1)
        
        If Intersect(r, Range("A5:B104")) Is Nothing Then Exit Sub
        If Len(r.Value) = 0 Then Exit Sub
        
        If Len(r.Offset(1, 0).Value) = 0 Then
            Set r = Cells(Rows.Count, r.Column).End(xlUp)
        Else
            Set r = r.End(xlDown)
        End If
        
        If Abs(r.Value) > 0.25 Then MsgBox "Aggregate value has crossed the tolerance limit (" & r.Address & ")"
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    VBAX Contributor
    Joined
    Jun 2019
    Posts
    155
    Location
    It is not working. The message is displaying for all the cells. The message is to be shown only if last cell is more than .25

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    If you change any of the BLUE cells, the last cell (GREEN) is always < .25, so no message


    Capture.JPG


    If you add a value at the bottom of the range > .25 (RED) the message shows

    Capture2.JPG


    If the last cell is > .25 the message always shows, even if the changed cell (ORANGE) is < .25

    Capture3.JPG


    What exactly are you asking for?
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    VBAX Contributor
    Joined
    Jun 2019
    Posts
    155
    Location
    Yes. I want the same. But in message the cell value to be update instead of cell range

  8. #8
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Ahhh -- when you said 'last cell' I thought that you meant 'last cell'

    Option Explicit
    
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim r As Range
        
        Set r = Target.Cells(1, 1)
        
        If Intersect(r, Range("A5:B104")) Is Nothing Then Exit Sub
        
        If Abs(r.Value) > 0.25 Then MsgBox "Aggregate value has crossed the tolerance limit (" & r.Address & ")"
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

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