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....
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....
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 -
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".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 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.
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
I mean range a1: s73
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
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?
Bookmarks