Results 1 to 7 of 7

Thread: Pop-Up Message Box when a Cell reaches certain Value

  1. #1
    Join Date
    Feb 2009
    Posts
    32

    Pop-Up Message Box when a Cell reaches certain Value

    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....

  2. #2
    Join Date
    Apr 2008
    Posts
    4,642

    Re: Pop-Up Message Box when a Cell reaches certain Value

    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 -

    Code:
    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".

  3. #3
    Join Date
    Apr 2008
    Posts
    4,642

    Re: Pop-Up Message Box when a Cell reaches certain Value

    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.

  4. #4
    Join Date
    May 2011
    Posts
    2

    Re: Pop-Up Message Box when a Cell reaches certain Value

    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

  5. #5
    Join Date
    May 2011
    Posts
    2

    Re: Pop-Up Message Box when a Cell reaches certain Value

    I mean range a1: s73

  6. #6
    Join Date
    Nov 2008
    Posts
    1,185

    Re: Pop-Up Message Box when a Cell reaches certain Value

    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:
    Code:
    Private Sub Worksheet_Calculate()
    If Range ("A10").Value < 100 Then MsgBox "Hi, I am below 100!!” 48, "Warning..."
    End Sub

  7. #7
    Join Date
    Nov 2011
    Posts
    1

    Re: Pop-Up Message Box when a Cell reaches certain Value

    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):


    Code:
    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?

Similar Threads

  1. How to print text message from cell phone
    By Pandya in forum Windows Software
    Replies: 6
    Last Post: 11-11-2013, 07:56 AM
  2. Replies: 2
    Last Post: 17-02-2012, 07:52 PM
  3. Replies: 5
    Last Post: 08-05-2010, 09:29 AM
  4. Processor that reaches 60 °?
    By C.M.D in forum Overclocking & Computer Modification
    Replies: 3
    Last Post: 19-12-2008, 07:21 PM
  5. Sapphire HD4850 reaches 110 º
    By Volodymyra in forum Monitor & Video Cards
    Replies: 2
    Last Post: 06-12-2008, 05:59 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,710,821,781.09615 seconds with 16 queries