Custom Data Validation error message using value from a cell

[Harryf]

Hi everybody!

In a spreadsheet, cell A1 calculates an amount. This amount is the maximum allowed that an investor may withdraw from an investment and is shown to the investor in Cell A1 as a "warning" of some kind. I actually have text running from B1 across various columns to the right stating that the value shown in A1 is the maximum allowed.

In cell A2, I ask the investor the actual amount that s/he would like to withdraw. But people don't always read instructions and someone invariably enters an amount exceeding the value indicated in A1...

So set up Data Validation in A2 to ask the investor to focus and to re-enter the amount but to limit it to the amount shown in A1, should the amount entered in A2 be bigger than the amount in A1 - but I can only use the words "cell A1" in the error message and not the actual amount, which will differ depending on the amount invested.

Let's assume the amount in A1 is calculated as $1, 000. This is the maximum allowed. Should an investor enter anything bigger than that in A2, my current Data Validation error message would pop up. It currently reads "The amount entered in this cell may not exceed the value indicated in cell A1. Please re-enter."

I would like it to read "The amount entered may not exceed $1, 000. Please re-enter", $1, 000 being the variable value drawn from cell A1.

Is this even possible?

Looking forward to your replies!

Cheers!

Harry

 

[sparky2205]

I don't believe this is possible. As far as I'm aware you can't link a cell to a Data Validation message.
The best you can probably achieve is to put your warning in the Input Message. That way when the user selects cell A2 to make an entry the message is popped up. In my experience users are more likely to read a popup message than text that is permanently displayed.

 

[sparky2205]

An alternative I thought of.
In cell B1: =IF(A2>A1,"The amount entered exceeds the max amount allowed of " & DOLLAR(A1,0),"")
You could format the message to appear in red font. The message will only appear when the max amount is exceeded.
The user is more likely to read a message that suddenly appears in red.
The drawback is this won't prevent the user entering the greater value.
Just an option.

You could also use Conditional Formatting to make the font colour of A2 red under the same conditions i.e. A2>A1

 

[Harryf]

Thanks Sparky!

I guessed as much... I actually thought to remove any reference to the reason for the figure in cell A1 when reading your post, but I guess it may cause more confusion if I just place a number randomly in a spreadsheet without indicating what it is... I do, however, believe that you are right and that someone may wonder why a lone figure is there, but once there is text next to it, not even care to read the text... Strange how our minds work... hehe

I'll just keep the error message, then. Thanks again!

 

[Snakehips]

@Harryf If you are able to use vba then try pasting this into the sheet's code module within the vba editor..

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub
If Target.Count = 1 Then

    Limit = FormatCurrency(Range("A1"))
     With Range("A2").Validation
            .Delete
            .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:="0", Formula2:="=A1"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""   '<<< Edit to suit
            .ErrorTitle = "Oi Mush!  Can You NOT Read?"  '<<< Edit to suit
            .InputMessage = "" '<<< Edit to suit
            .ErrorMessage = _
            "The amount entered may not exceed   " & FormatCurrency(Range("A1")) & "  Please re-enter"   '<<< Edit to suit
            .ShowInput = True
            .ShowError = True
        End With
End If
End Sub

Hope that helps.

 

[Harryf]

Sorry... Missed your second post Sparky... Good idea about the IF function and the conditional formatting, but like I said above, I have to force a correct number in A2 and the message won't prevent someone ignoring it... Cheers!

 

[Harryf]

Hello Snakehips!

Great!!! Thank you very much!! I changed the cell references and error messages to suit my actual spreadsheet and it works like an absolute charm!

There you go Sparky! We met the Master... ??

 

[Snakehips]

Hi Harry ! Pleased we could help.

You may wish to try the following variation that should give a popup message on selection of the input cell.

Private Sub Worksheet_Calculate()
Range("A2").Validation.InputMessage = "PLEASE Enter an amount NOT greater than  " & FormatCurrency(Range("A1")) '<<< Edit to suit
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub
If Target.Count = 1 Then

    Limit = FormatCurrency(Range("A1"))
     With Range("A2").Validation
            .Delete
            .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:="0", Formula2:="=A1"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""   '<<< Edit to suit
            .ErrorTitle = "Oi Mush!  Can You NOT Read?"  '<<< Edit to suit
            .InputMessage = "PLEASE Enter an amount NOT greater than  " & FormatCurrency(Range("A1").Value) '<<< Edit to suit
            .ErrorMessage = _
            "The amount entered may not exceed   " & FormatCurrency(Range("A1")) & "  Please re-enter"   '<<< Edit to suit
            .ShowInput = True
            .ShowError = True
        End With
End If
End Sub

 

[Harryf]

Ah! That is cool! Yes, I am going to add that snippet of code...

Thanks Snakehips!