Data Validation-Show an error.....

  • Thread starter Thread starter lunker55
  • Start date Start date
L

lunker55

I have a cell that is summing from another sheet in the same workbook. When
this cell hits 90.5 or above, I need an error window to show up. It only
seems to work if I enter a number directly into the cell.

Can this be done?

Joe
 
Hi Joe
For this you'll need VBA: Try the following code (put this in your
worksheet module):
Private Sub Worksheet_Calculate()
If Range("C1").Value >=90.5 Then
MsgBox "Error in cell C1"
End If
End Sub
 
I have noticed this myself
I believe Data Validation is for direct input
You will need to put validation on another input cell to restrict unwanted results

----- lunker55 wrote: ----

I have a cell that is summing from another sheet in the same workbook. Whe
this cell hits 90.5 or above, I need an error window to show up. It onl
seems to work if I enter a number directly into the cell

Can this be done

Jo
 
Thank you frank. Works great!
How do I loop it so it does the same for all of column C?

Joe
 
Hi
try (not fully tested):

Private Sub Worksheet_Calculate()
Dim cell as range
dim rng as range

set rng = Range("C1:C100")
for each cell in rng
if cell.value >=90.5 then
msgbox "Value to high in cell C" & cell.row
end if
next
End Sub

Though be careful to use this - You should better restrict the other
cells which cause these high values
 
Thank you very much Frank.
Works perfectly.
I see what you mean- I can't have any of the cells above that value. Is
there a message box that opens up as a warning that gives an option to
overrride instead? If there is, don't give me the code. I just want to know
if it can be done.
Thanks again for your time.

Joe
 
Hi
this currently creates only a warning message. You can have higher
values in these cells you just get a messagebox each time the sheet
re-calculates.
But if you want some code that stores your chioce for each cell (if it
is allowed to have higher values) It is getting a little bit more
complicated. You may try the following:

Option Explicit
Private Sub Worksheet_Calculate()
Static test_not_required(100)
Dim cell As Range
Dim rng As Range
Dim ret

Set rng = Range("C1:C100")
For Each cell In rng
If cell.Value >= 90.5 And Not test_not_required(cell.row) Then
ret = MsgBox("Value too high in cell C" & cell.row & Chr(13) _
& "Continue checking this cell", vbYesNo)
If ret <> 6 Then
test_not_required(cell.row) = True
End If
End If
Next
End Sub
 
Back
Top