If a column value equals 25, pop up message

[ibilees]

I am pretty sure this is very easy and i found a code that did this sorta thing on this website. BUT...what it did was it focuses only on one cell, while i need it to check the whole column or at least a range of cells within a column. lets say column B and if a cell value in column B falls within a certain parameter i need to have a message box pop up.


For example. if a cell in column B OR a range of cells lets say "B1:B22" equals 25, I want a message box to pop up that says "limit reached".


Plus heres the script i found... it works as a charm for a single cell but not like i want it to.


Please advice i know its a matter a simple code tweaking. Thanks!


Code: (http://www.mrexcel.com/forum/excel-questions/98992-if-cell-value-equals-0-pop-up-message.html)


Option ExplicitPrivate Sub Worksheet_Calculate()Dim Rng1 As RangeDim Value As DoubleDim Prompt As StringDim Title As String 'Put the range you want to look at here Set Rng1 = Range("B12") 'Put the target value here Value = 0 'Put the message (prompt) of the message box (pop up) here Prompt = "Congratulations, You Balance!!" 'Put the title of the message box (pop up) here Title = "Balance" If Rng1.Value = Value Then MsgBox Prompt, vbInformation, Title End IfEnd Sub

 

[Joe4]

Welcome to the Board!

Here is what the cleaned up code your tried to post looks like:

Option Explicit


Private Sub Worksheet_Calculate()


    Dim Rng1 As Range
    Dim Value As Double
    Dim Prompt As String
    Dim Title As String
    
    'Put the range you want to look at here
    Set Rng1 = Range("B12")
    
    'Put the target value here
    Value = 0
    
    'Put the message (prompt) of the message box (pop up) here
    Prompt = "Congratulations, You Balance!!"
    
    'Put the title of the message box (pop up) here
    Title = "Balance"
    
    If Rng1.Value = Value Then
        MsgBox Prompt, vbInformation, Title
    End If
    
End Sub

For example. if a cell in column B OR a range of cells lets say "B1:B22" equals 25, I want a message box to pop up that says "limit reached".

They made this pretty easy for your to do this. Just change the parameters, i.e. change this part:

    'Put the range you want to look at here
    Set Rng1 = Range("B12")
    
    'Put the target value here
    Value = 0
    
    'Put the message (prompt) of the message box (pop up) here
    Prompt = "Congratulations, You Balance!!"
    
    'Put the title of the message box (pop up) here
    Title = "Balance"

to

    'Put the range you want to look at here
    Set Rng1 = Range([COLOR=#ff0000]"B1:B22[/COLOR]")
    
    'Put the target value here
    Value = [COLOR=#ff0000]25[/COLOR]
    
    'Put the message (prompt) of the message box (pop up) here
    Prompt = "[COLOR=#ff0000]Limit Reached[/COLOR]"
    
    'Put the title of the message box (pop up) here
    Title = "[COLOR=#ff0000]Limit Reached[/COLOR]"

 

[ibilees]

Thanks joe4. I'm kinda new to this forum so sorry for the messy post.

I forgot to mention it...but i did it try it that way earlier n all i get is an error. The debugger points to the range line. So i. Suspect the problem is with that line. Can you please confirm it for me.

 

[Joe4]

Ah, we probably have to loop through each cell in the range.
Out of curiosity, I tried running the original code you posted to see if that works, and I couldn't even get that to work. Did you get that to work for you?

In any event, I would probably be more comfortable creating my own code from scratch. But I want to understand a little more what you want to happen.
How exactly is data entered into B1:B22?
When do you want this code to actually run (upon data entry, or after the fact)?

 

[ibilees]

Well I used the same code from the page and it happens to work fine for me but as intended. Anywhoo my actual problem goes something like this.

I enter my data on Column A and a simple calculation is made on Column B. If any value in column B is greater than or equal to a particular number say 25, I want a message box to pop up that says "limit reached". I don't know how many rows of cells will be actually used but I believe it wouldn't be any more than 60...i.e. "B1:B60" (I used B1:B22 as just an example).

I want the code to run upon data entry (as soon as I open the file) and on the open worksheet only. I would really like if the code is narrated as the code above so I could change the parameters later on if need be and plus I really want to learn how do it later.

Hope you understand the problem. Thanks a lot.

 

[Joe4]

I think I may use Worksheet_Calculate event procedure that runs whenever column A is updated, and checks the value in column B, like this:

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim myRange As Range
    Dim myValue As Double
    Dim myTitle As String
    Dim myMessage As String
    
'   Enter range you want to trigger code when data is entered:
    Set myRange = Range("A1:A60")
    
'   Enter value to check for
    myValue = 25
    
'   Enter Title of message box
    myTitle = "WARNING!"
    
'   Enter message of message box
    myMessage = "Limit reached"
    
'   Check to see if cell updated is in our pre-defined range
    If Not (Intersect(Target, myRange) Is Nothing) Then
'       Check the value of the cell one column to the one where data is being entered
        If Target.Offset(0, 1) = myValue Then
            MsgBox myMessage, vbOKOnly, myTitle
        End If
    End If
           
End Sub

 

[ibilees]

Joe4 this is excellent! Thanks a bunch! You've been a lot of help!

Cheers! :D