message box pop up based on if statement

[DMT]

Hi,

I would like a message box to pop up based on an if statement. I think that I need to write code for this, but I don't know how. Does someone know the code and also could someone give me instructions on how to implement the code (ie do I just paste the code in to a new module)?Thanks.

 

[SydneyGeek]

You could do something like:

Sub ShowMsg ()
If Range("A1").Value > 1000 Then
   MsgBox "Getting too big!"
End If 
End Sub

Paste this into a new or existing module, put some different values in A1, and run the code.

Is that what you were after?

Denis

 

[kkknie]

I think the message box you want needs to be triggered by code in an event procedure (kind of a built-in trigger). You'll have to elaborate a bit more before I can help.

1. When do you want the message to pop up (user entering a value in a cell, worksheet changing, ???).
2. Do you want the user to interact with the message. For example, if they enter yes, do something and no do something else.
3. Do you need more than just the basic message box. If you need much more you'll need a userform (easy stuff).

Please post back. I'll be online for another 30 minutes or so...

K

 

[DMT]

Denis,

Your code worked only if I ran it by going to Tools>Macros>Macro>Run. I would like the macro to work (and the message to pop up) right after the person enters the number. Is this possible?

----------------------------------------------------------

I want the message box to pop up after the user enters a vlue into a cell. For example, the user enters a number into a cell. Another cell has an if statement based on that cell. If the if statement comes up false, I want a message box warning the person. If the if statement comes up true, then no message box will appear. Thanks.

 

[kkknie]

Under the worksheet code section (right-click sheet tab and select view code), paste the following.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("A55").Select
End If
End Sub

Modify the $A$1 to suit your needs.

K

 

[SydneyGeek]

Try this:

 Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then 
   If Target.Value > 1000 Then
      MsgBox "Getting too big!"
   End If
End If
End Sub

Denis

 

[DMT]

That's very close to my answer. Now, one wrinkle. What if I want the message box to appear based on a cell that is a formula. So instead of typing a number greater than 1000 to trigger the message box, a cell is linked to another and if the cell with the formula goes over 1000, then the message appears. I hope this is clear! Basically, instead of the check based on the input, it is based on the output of a formula.

 

[Nimrod]

DMT:
This can be done quite easily using "Data Validation" found on the Tool Bar under "Data".

For example ...

1. Select A2 then go to tool bar...data...data validation
2. IN data validation ...
... Select "Settings Tab"
......under "Allow" select Custom
........under "Formula" type in .. =B2<1000
.......... on the "Error Alert" Tab type your popup message
........... DONE in the validation window ..press OK

3. IN B2 place your formula

Now when you place a value in A2 that makes the value in B2 go over 1000 you will get a pop up message ..


ADVANTAGES TO METHOD..
1. VBA does not need to be activated in sheet to work
2. Data validation allows the messge to either stop entry or just warn by using the "Style" menu on the Error message Tab in the Validation set up
3. Requires no code
4. Quick easy :wink: