Can a message box to appear due to a value in a cell

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

Hi,
I would like to know if it is possible for a message box to appear when a
cell becomes a certain value and how that would be done.
Rob
 
AFAIK, this can only be done using some VBA programming.

First, you need to understand a little about events.

http://www.mvps.org/dmcritchie/excel/event.htm
http://www.cpearson.com/excel/events.htm

You would be interested in one of two WorkSheet Events:

Worksheet_Calculate :
This event occurs then a cell on the worksheet is calculated.

Worksheet_Change :
This event occurs when the value of a cell is changed. This event
does not occur when the value is changed as the result of a
calculation.

I'm assuming that the cell changes value when you manually enter a new
value. Say in cell A1. And the trigger value is 6.

(1) Right click on the WorkSheet tab.
(2) View Code.
(3) Place the following code snippet in the module.
(4) Change the value in Cell A1 to 6.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Value = 6 And Target = Range("A1") Then
MsgBox "I've changed."
End If

End Sub

HTH
Paul
 
rob,

You may also want to try data validation. Suppose you want to show the
message when the user enter a number greater than 10 in A1.

1. Select A1
2. Data|Validation
3. settings - Allow: Decimal, Data: Choose less than or equal to,
Maximum: 10
4. input message - You can put any message that you want to appear when cell
A1 is selected (Also nothing if you want)
5. error alert - You can put here the message you want to appear when user
enter number greater that 10. Take note of the style. Stop
will not accept the user input while the other two will accept it. Its all
yours to decide.

Regards,

Jon-jon
 
You may be able to use Data Validation with which you normally set a
message in case of input error.

Regards
BrianB
=================================================
 
Hi Paul,
Thanks for this. In theory it seems that this is exactly what I'm after.
Only problem is that I can't get it to work. I opened a new workbook and
clicked on sheet 1, entered 6 into A1 and pasted the code in as you said.
I can't get the code to "run to curser" nor "step into" on the debug menu
either. Just get a default beep. (I also made sure there were no blank
spaces between lines just in case.)
Another query..... if I have cell A1 become 6 due to a value changing in
another cell, does this procedure not work? If it doesn't, is there a code
that will do that?
Rob
 
Back
Top