popup alert in data validation

[karl]

hello,

i need to set up an "indirect" error alert using DATA VALIDATION. please
refer to the following link http://www.freefilehosting.net/download/3h8gj
details are provided in the uploaded file.

thank you,
karl

 

[Roger Govier]

Hi Karl

I don't understand why you are not prepared to have data Validation on cell
D4.

Apply DV and set it to allow Whole Number between Minimum 0 and Maximum 50
Set the message on the error alert tab to something like
"The value in this cell must be less than 50"

There will be no dropdown on the cell, but if anyone tries to enter a value
greater than 50, they will be prevented and your message will pop up.

 

[karl]

the reason I don't want to validate D4 is that it is a sum of two numbers and
I won't be able to select that cell directly. I hope I answered your
question.

 

[Roger Govier]

Hi Karl

I now understand.
You cannot use DV error box, unless it is applied to that cell.

You could incorporate some VBA code to check it, and bring up an error
message, but you need an event to trigger it.
The following code would be triggered if you double click anywhere on the
sheet.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Range("D4").Value > 50 Then
MsgBox "Error in cell D4. Value too high"
End If
End Sub

Copy the code above
Right click on Sheet tab>View Code>Paste into the white pane.
Alt+F11 to return to Excel

 

[karl]

Hi Roger,
I must be doing something wrong. I hit alt f11 and pasted the code. the
first sentence (Private Sub Worksheet_BeforeDoubleClick(ByVal Target As
Range, Cancel As Boolean)) is all red when i paste it.
thanks.

 

[Roger Govier]

Hi Karl

The NG reader has wrapped it onto 2 lines.
In the code window, hit the delete button at the end of line 1, and that
will bring it all back to one line and it will change from Red to Black.

or use the code posted below with a proper line break in it

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Range("D4").Value > 50 Then
MsgBox "Error in cell D4. Value too high"
End If
End Sub

 

[karl]

Roger, Thank you. I later realized that I had to double click on the cell in
order to get the popup window.
please help me to have a popup window as soon as the wrong data is entered,
without having to return to the cell and double clicking on it- just as the
"ERROR" shows up in d6, in my original example.

thank you in advance.

 

[Roger Govier]

Hi Karl

We need an event to trigger the macro.
From what you had said previously, the value in D4 is a formula, based upon
entries in other cells
Let me know what the formula is in D4, so I can see which cells it is
dependent upon.

 

[karl]

here we go Roger: http://www.freefilehosting.net/download/3hbeh
thanks.

 

[Roger Govier]

Hi Karl

I was not thinking correctly.
As there is a formula in D4, which will get recalculated when there is a
change on the sheet, then you could use

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("D4").Value > 50 Then
MsgBox "Error in cell D4. Value too high"
End If
End Sub

If for any reason the cell doesn't get calculated each time, then you could
force volatility by changing the formula to
=C1/C2+NOW()-NOW()

 

[karl]

hi roger,

thank you very much!! it works great.

 

[karl]

Hi Roger,

Suppose I want to use that code for ten cells in one worksheet. I have tried
to do so but couldn't accomplish much.
I have attached an example as well.
http://www.freefilehosting.net/download/3ibkh
thanks

 

[Roger Govier]

Hi Karl

try the following code to use with a Double Click event

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim myrange As Range, cell As Range

Set myrange = Range("D4, I4, N4") '<=== Extend as necessary
For Each cell In myrange
If cell.Value > 50 Then
MsgBox "Error in cell " & cell.Address & vbCr _
& "Value too high"
End If
Next
End Sub