-----Original Message-----
Chong, Thanks for the code, it works when I put it in the
worksheet coding. Can we make this code work in the
workbook module? (saves me a lot of time implementing in
the 33 worksheets) The four (of seven) worksheets in
question are: "Customer", "Financial", "Learning and
Growth", and "Internal Processes". Also, the incorrect
condition remains in place with the [M15]<[M16]... code.
Can we add a line that makes clearing the incorrect
condition manditory before continuing with additional
input?
Phli
-----Original Message-----
Copy below to the sheet module... You could define your
requirements as how I have it or as how Bob Kilmer has
it. The code below uses the Worksheet_Change event.
Anytime the sheet changes the code will automatically run.
------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If [M15] < [M16] Then MsgBox "Target cannot be greater
than Chart Max"
....
....
....
End Sub
-----------------------
-----Original Message-----
Something like this?:
Option Explicit
Public Sub CheckValues()
Dim msg As String
If _
Range("M15").Value <= Range("M16").Value Or _
Range("M47").Value <= Range("M48").Value Or _
Range("M79").Value <= Range("M80").Value _
Then
msg = "Chart Max must be greater than Target"
ElseIf _
Range("M16").Value <= Range("M18").Value Or _
Range("M48").Value <= Range("M50").Value Or _
Range("M80").Value <= Range("M82").Value _
Then
msg = "Target must be greater than UCL"
'ElseIf _
'...etc.
Else
'??
End If
MsgBox msg
End Sub
These range references refer to cells on the active worksheet. You may want
to identify the worksheet more specifically. You could put this into a
standard module. What is going to call the code?
It may also be possible to setup data validation in a multitude of workbooks
like Chong Moua suggested, but do it programmatically.
I
do not know Excels
data validation functionality that well, but if I
wanted
to know, I'd record
a few macros and read a little to find out.
--
Bob Kilmer
I agree - for a single workbook; however, I have to change
33 workbooks - 594 changes. I really do need to do this
in the module where I can copy/paste the solution. Can
someone help?
-----Original Message-----
Hi Phil,
You don't have to write codes for this. You can use data
validation to accomplish the same thing.
Hope this helps...
Chong Moua
-----Original Message-----
On four worksheets in a workbook, the following cells
have
theese typical values, and text:
Cells Value Name
M15/M47/M79 - 110 - Chart Max
M16/M48/M80 - 100 - Target
M18/M50/M82 - 75 - UCL
M22/M54/M86 - 70 - LCL
M26/M58/M90 - 60 - Op Zero
M29/M61/M93 - 45 - Chart Min
M15 must always be greater than M16, M16>M18, etc. M47
must always be greater than M48, M48>M50, etc. Same for
the M79 cells. What would the code be and where do
I
put
it) where a message comes up indicating the error? For
example: if M22 = 70, and M18 = 75, the message says
that "UCL cannot be greater than LCL", the user clicks
ok,
and then fixes the error.
Would I put the code in Module1?
Thanks, Phil
.
.
.
.
.
