Results 1 to 4 of 4

Warning box if range value > 0

This is a discussion on Warning box if range value > 0 within the Excel Questions forums, part of the Question Forums category; I need a message box to pop up if the value of one of the cells on a sheet is ...

  1. #1
    Board Regular
    Join Date
    Mar 2007
    Posts
    117

    Default Warning box if range value > 0

    I need a message box to pop up if the value of one of the cells on a sheet is greater than 0. The range is B3 to B7. Now I have 5 different sheets in this work book, so lets say on sheet 1 the value of the range is gretaer than 0, I need a pop up box that says "check sheet 1". And if on sheet 2, same affected range is greater tham 0, then a pop up box that says "check sheet 2". (These sheets are updated based on values from a different workbook)

    Here is a sample code I was trying to work with, placed in the "ThisWorkbook" location. (I think that's the correct place to put this??)


    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Sheets("SH 5").Select
    If (Range("B3:B7").Value > 0) Then
        MsgBox "Check sheet 5"
    End If
    End Sub

  2. #2
    Board Regular mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    2,907

    Default Re: Warning box if range value > 0

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Sheets("SH 5").Range("B3:B7").Value > 0) Then   MsgBox "Check sheet 5"
    End Sub
    I think that could work
    Yes I know there are better ways to do it. I just wish I knew them.

    I wear my ignorance openly, excel is not my chosen career, its a means to an ends

    Want to post well laid out questions and answers
    Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED

  3. #3
    Board Regular
    Join Date
    Mar 2007
    Posts
    117

    Default Re: Warning box if range value > 0

    I can't get it to work, if I post it in a macro to run, I get an error.
    I changed the range to only B5 where I know the value is "1", and it worked.
    So I'm assuming it's having trouble adding the range B3:B7....
    Not sure if there should be an extra step in there to add the range???

    Thanks

  4. #4
    Board Regular mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    2,907

    Default Re: Warning box if range value > 0

    I must be mad, i'm sure there is a better way of doing this

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    On Error Resume Next
    
    If Sheets("SH 5").Range("B3:B7").Value > 0 Then   MsgBox "Check sheet 5"
    End Sub
    Yes I know there are better ways to do it. I just wish I knew them.

    I wear my ignorance openly, excel is not my chosen career, its a means to an ends

    Want to post well laid out questions and answers
    Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com