Pop-Up Message Box when a Cell reaches certain Value

[Eleazar]

hie,

When I am using Excel to enter the data - values or text in the cells, I want to make the settings such that when a cell reaches certain value or contains certain text, a message box should pop-up. How can I make these settings ?
Any ideas....

[Eric B]

You can make the required settings by using a facility called macros...

Here's the macro code to make message box available for pop-up under the required conditions -

Private Sub Worksheet_Change(ByVal Target As Excel.Range)    

Dim rng As Range
   Set rng = Range("A1")
   If Not Intersect(Target, rng) Is Nothing Then
       If rng = "hi" Then
        MsgBox "Cell " & _
         rng.Address & " = hi"
       End If
   End If

   Set rng = Nothing

End Sub

This macro will display a message box in excel when a cell reaches a certain value or contains certain text. This means that when cell A1 contains the value "hi" a message box will pop-up telling the user that cell A1 now contains the word "hi".

[Eric B]

This macro can be changed to display a message box whenever a cell meets whatever criteria you want. You can change the criteria to display a message whenever certain text is entered into a cell by changing ,in this line of code, If rng = "hi" Then the "hi" to whatever you want. You can also change the display message to whatever you would like by changing the text inbetween the quotation marks in this code MsgBox "Cell " & _ rng.Address & " = hi". Also, if you want to change the cell to which the value must be entered for a message box to appear, change the A1 in this line of code to whatever you would like Set rng = Range("A1").

This macro is particularly useful in forms in excel. This way you can solicit input from a user and when he/she enters something, you can have a message box appear to remind them of something or to warn the user etc. This is pretty versatile code and is easily modified to check for many different types of values or characters and to then display the pop-up message box in excel.

Note: This macro goes into the Worksheet Code section of the vba editor.

[madaleck]

Hi, this code is very useful but how do i amend it be run when the range is A173 ??

I dont want it to be just one cell, even the whole worksheet would work for me?

Thanks

[madaleck]

I mean range a1: s73

[DotNetUser]

Just try if this helps. Right click on your sheet tab, left click on View Code, and paste the below process into the large white part that is the worksheet module. Push Alt+Q to go back to the worksheet. Adapt for cell address and message box text and the value you wish to be warned about. It seems like the cell of interest encloses a formula, based on your narrative, so a Calculation event might be what you require:

Private Sub Worksheet_Calculate()
If Range ("A10").Value < 100 Then MsgBox "Hi, I am below 100!!” 48, "Warning..."
End Sub

[SamF]

I am trying to display a WordArt when the value in A1 is < 0.01 or blank.

It works when the value in A1 is changed, however when any other cell is changed or edited, the WordArt displays as well (despite what is in A1):

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
   
Dim MyRange As Range
Set MyRange = Range("A1")

Set WAshape = ActiveSheet.Shapes.AddTextEffect(msoTextEffect14, _
    "Enter Value in A1", "Impact", 24#, msoFalse, msoFalse, 118.5, 120.8)

    If Not Intersect(Target, MyRange) Is Nothing Then

        ' Delete all shapes
        If MyRange >= 0.01 Then
            Set myShapes = ActiveSheet.Shapes
            For Each shp In myShapes
                If Left(shp.Name, 7) = "WordArt" Then
                    shp.Delete
                End If
            Next

        ElseIf MyRange = "" Or Target.Value = 0 Then
            WAshape.Visible = True
        End If
        
    End If
    
     Application.EnableEvents = True

End Sub

It works if A1 is changed (and displays/disappears appropriately), however if any other cell is changed the WordArt displays.

How can correct this so only change based on A1?