Good code not working???

[Phil Hageman]

The object with this code is that in either of the
worksheets, if the user enters "100" in M15, and "120" in
M16, the message comes up. Nothing happens when these
test values are entered. Furhter, there are no error
messages. Can someone help with this? Does the value in
the MyArray have anything to do with it?

Sub Descending_Values()

Dim MyArray() As Integer
ReDim MyArray(3) ' or, the maximum characters in the
target cells
MyArray(3) = 3

If (Worksheets("1. Customer").[M15] < [M16] Or _
Worksheets("2. Financial").[M15] < [M16]) Then
MsgBox "Target cannot be greater than Chart Max"
End If
End Sub

Thanks, Phil

 

[Phil Hageman]

If this is a change event, what would the code be?

-----Original Message-----
What is supposed to make Descending_Values run. If you want it to validate
an entry when it is made, you would have to trigger it with the change
event.

If you run the macro as written through Tools=>Macro=>Macros, does the
msgbox appear when it detects a situation in which you would expect it to
appear?

--
Regards,
Tom Ogilvy

I added the "fully qualify" syntax and the error condition
is still allowed - with no error messages concerning
code. As for the array, I did that in response to a
previous error/help solution. I have eliminated the array
stuff, and here is what I have:

Sub Descending_Values()


If (Worksheets("1. Customer").[M15] < Worksheets("1.
Customer").[M16] Or _
Worksheets("2. Financial").[M15] < Worksheets("2.
Financial").[M16]) Then
MsgBox "Target cannot be greater than Chart Max"
End If
End Sub

This code is located in Module1 of the workbook, and
operates on two (Cust, Fin) workksheets of ten in the
workbook.

What can I do next?

Phil

-----Original Message-----
Phil,

You need to fully qualify all cell references with the worksheet
reference:

If (Worksheets("1. Customer").[M15] < _
Worksheets("1. Customer").[M16] Or _
Worksheets("2. Financial").[M15] < _
Worksheets("2. Financial").[M16]) Then
MsgBox "Target cannot be greater than Chart Max"
End If

Does the value in
the MyArray have anything to do with it?

You aren't using the array anywhere in the code. Why is it even
there?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)


The object with this code is that in either of the
worksheets, if the user enters "100" in M15,

and "120"
in

M16, the message comes up. Nothing happens when these
test values are entered. Furhter, there are no error
messages. Can someone help with this? Does the

value
in

the MyArray have anything to do with it?

Sub Descending_Values()

Dim MyArray() As Integer
ReDim MyArray(3) ' or, the maximum characters in the
target cells
MyArray(3) = 3

If (Worksheets("1. Customer").[M15] < [M16] Or _
Worksheets("2. Financial").[M15] < [M16]) Then
MsgBox "Target cannot be greater than Chart Max"
End If
End Sub

Thanks, Phil


.

.