Code for msg box
 

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, M16M18, etc. M47
must always be greater than M48, M48M50, 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

Code for msg box
 

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


"Phil Hageman" wrote in message
...
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, M16M18, etc. M47
must always be greater than M48, M48M50, 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
.

.

Code for msg box
 

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


"Phil Hageman" wrote in message
...
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, M16M18, etc. M47
must always be greater than M48, M48M50, 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
.

.



.

Code for msg box
 

Bob, Thanks for your reply. I like the format you use.
Presume this is added to the workbook module1. Can we
modify it - there are seven worksheets, however, this code
applies to only four: "Customer", "Financial", "Learning
and Growth", and "Internal Processes". Also, I need
something that requires correction of the error condition
before the user procedes - or exits the worksheet.

Thanks, Phil
-----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


"Phil Hageman" wrote in message
...
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, M16M18, etc. M47
must always be greater than M48, M48M50, 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
.

.



.

Code for msg box
 

Phil,

Copy this to workbook module...
----------------------------
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal
Target As Range)

If [M15] < [M16] Then
MsgBox "Target cannot be greater than Chart Max"
[M16].Clear
End If

End Sub
-----------------------------
Hope this helps...

Chong Moua

-----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 a "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


"Phil Hageman" wrote in message
.. .
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, M16M18, etc.
M47
must always be greater than M48, M48M50, 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
.

.



.

.

.