Data Validation Input Message

K

Klee

I tried to get back and reply on my last question but was unable to so I had
to start a new question. I'm trying to use the data validation to make a pop
up come up in a cell (e7) if two criteria are met. One is if B3="General
Expenses" and the other is if e7 is more than $500.

I got a couple of formulas from my previous question. The problem is that as
soon as you click on the cell the message pops up. This happens whether the
cell is blank or over or under the $500 and regardless if B3 says "General
Expenses" or not.
Is there a way to get the pop up only if the other criteria are met? Thanks
again!
 
D

Dom_Ciccone

I've just created a new workbook and entered the validation in cell E7 as

=IF($B$3="General Expenses",IF(E7>500,FALSE,TRUE),TRUE)

This is the same formula I posted earlier. Make sure "Ignore Blank" is
checked. This formula works fine on mine. Are you sure you entered it
correctly (sorry I know that's a basic question but it really does work on
this machine).

The formula displays an error if B3 contains "General Expenses" and you
attempt to enter a value over 500. If you enter a value equal to or less
than 500 the error message does not appear. If however, cell B3 does NOT
contain the phrase "General Expenses", then the error message will not appear
no matter what value you use in cell E7.
 
K

Klee

Yes, I did enter the formula correctly but it's a question that's worth
asking.

I have several different formulas in E8,E9 ect to see what works. Yours is
great except for the error. I want people to be able to enter an amount in
the cell regardless of the amount. I just want the pop up to let them know
that they need to fill out a different form when the amount is over $500.
Sorry that I didn't explain that better earlier.

Any suggestions?
 
D

Dom_Ciccone

Ahhhh I see. I'm not aware of any way to do this In Excel itself. If you
are comfortable using VBA the probably the best place to do this would be
attached to the Worksheet_Change() event, checking that the Target cell is
within the range (E7:E60) and if performing your checks there. You could
then flag this to the user as a message box or add a comment to the cell and
display that.
 
K

Klee

I don't know much about VBA. If there is anyone who would be able to supply
me with the text to paste in it would be fantastic.

Thanks for the info though Dom, I would have just kept messing around with
data validation until my head exploded
 
D

Debra Dalgleish

You can use an Error message, instead of an Input message.
In the data validation dialog box, on the Error Alert tab, create a message.
From the Style dropdown, choose either Information or Warning.
Users will see the message, but will be able to enter any amount.
 
D

Dom_Ciccone

Here is the code you require. It is embellished a little but you can delete
the parts you don't want. I included things purely as an example. Open the
Visual Basic Editor and in the Project Explorer on the left find the workbook
you wish to add the code to. Double click the Sheet name of the sheet that
will contain your data and add this code to it:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim entryrange As Range
Set entryrange = ActiveSheet.Range("E7:E60")

If Not Application.Intersect(Target, entryrange) Is Nothing Then
If ActiveSheet.Range("B3").Value = "General Expenses" Then
If Target.Value > 500 Then

'This line adds a comment to the cell - delete if you wish
If Target.Comment Is Nothing Then
Target.AddComment ("Remember to complete form <NAME>")
End If

'This line displays a message box - delete if you wish
MsgBox ("Remember to complete form <NAME>")

'This line changes the cell colour - delete if you wish
Target.Interior.ColorIndex = 3
Else
If Not Target.Comment Is Nothing Then
Target.Comment.Delete
Target.Interior.ColorIndex = 0
End If
End If
End If
End If
End Sub


This does three things (slight overkill). It places a comment in the cell,
paints the cell a different colour and displays a message box as well. If
you go back and change the value in the cell to something lower than $500 it
will remove the comment and remove the fill colour from the cell.

Hope this helps you.
 
R

Roger Govier

Hi Deb

I'm ashamed to say that in 12 years of using Excel, I have never noticed
that Style dropdown.
 
K

Klee

Hi Dom,
I just saw this and tried it. It worked perfectly. Thanks very much for all
your time. I really appreciate it. I need to find a book to learn how to do
this. It's very interesting.
Thanks again,
 
Top