Data Validation

[Brian from Maui]

Aloha,

Is it possible to set a data validtion where that cell has the sum of other cells. I've tried using custom and using the formula to sum cells and greater than, but evertime test it with a value greater than what I want, I get no warning prompt.



Brian

 

[Dave Hawley]

Hi Brian

If you give me a realistic example I'll set it up so that it's error proof. Nothing worse than a Runtime error for an Excel user.

 

[Brian from Maui]

Dave,

The cells that have the sum formulas are I7 , I8, I12, I16 but they all have different > values, but I can figue that out.

Brian

 

[Dave Hawley]

Here you go

Private Sub Worksheet_Change(ByVal Target As Range)
With WorksheetFunction
If .Sum(Range("I7")) <> 1800 Or _
.Sum(Range("I8")) <> 1800 Or _
.Sum(Range("I12")) <> 1800 Or _
.Sum(Range("I16")) <> 1800 Then

MsgBox "Invalid value", vbCritical
Target.Select
End If
End With
End Sub

 

[Brian from Maui]

Dave,

Never mind I took out the< and just left the > and it works, except the use needs to use the delete key to move on. But that's minor hopefully.

Mahalo for the time and effort and patience.

Brian
This message was edited by Brian from Maui on 2002-03-16 19:47

 

[Dave Hawley]

Hi Brian

You could add:

Application.Undo
Just before the: Target.Select

This would eliminate the need to delete and would return the cell to it's original value


Private Sub Worksheet_Change(ByVal Target As Range)
With WorksheetFunction
If .Sum(Range("I7")) > 1800 Or _
.Sum(Range("I8")) > 1800 Or _
.Sum(Range("I12")) > 1800 Or _
.Sum(Range("I16")) > 1800 Then

MsgBox "Invalid value", vbCritical
Application.EnableEvents = False
Application.Undo
Target.Select
Application.EnableEvents = False
End If
End With
End Sub



And if all these cells should total 1801 each or greater you could use:

Private Sub Worksheet_Change(ByVal Target As Range)
With WorksheetFunction
If .Sum(Range("I7,I8,I12,I16")) > 1800 * 5 Then

MsgBox "Invalid value", vbCritical
Application.EnableEvents = False
Application.Undo
Target.Select
Application.EnableEvents = False
End If
End With

 

[Brian from Maui]

Dave,

I tried itthe first time and it worked, now it doesn't.

 

[Dave Hawley]

Oops!!! That's because of one of my typos, sorry.

The second part should read:
Application.EnableEvent=True

EG

Private Sub Worksheet_Change(ByVal Target As Range)
With WorksheetFunction
If .Sum(Range("I7,I8,I12,I16")) > 1800 * 5 Then

MsgBox "Invalid value", vbCritical
Application.EnableEvents = False
Application.Undo
Target.Select
Application.EnableEvents = True
End If
End With


To get it running again just place this in a standard Module and run it:

Sub ResetEvents()
Application.EnableEvents=True
End sub

 

[Brian from Maui]

Dave,

I'm sorry, but is my first time doing this, I right click on the tab and copy and paste in the work book,then how do I get to the amn module?

Brian

 

[Dave Hawley]

Sorry Brian, I shouldn't assume. Just go to Insert>Module