First posted a week ago to excel.programming. Thought I'd try again,
adding excel and excel.worksheet.functions hoping for a reply this time.


Office 2003 SP3 on XP Professional

Looking for advice as to where to start looking for my problem.

I have written a function to perform validity checking on selected
portions of my worksheet data. The new function works great, except that
as soon as I had tested it and added it to the conditional format
conditions on the relevant columns some of my other custom toolbar
macros began to misbehave.

I wrote a custom function so that I could test multiple (in excess of 6)
conditions and put the complexity of the formulas in VBA instead of long
and cumbersome conditional formatting formulas.

If I disable the conditional formatting -- either by closing the single
workbook that uses it, or by renaming the custom function -- all the
original macros return to normal behavior and function.

I set the conditional formating formula to:

=NOT(checkCyl(RCnn))

where nn is the column number under test. [It just occurred to me I
could use simply RC ... I'll test that.]

While investigating, I discovered this behavior - one of the custom
toolbar functions that misbehaves includes a call to this function (the
debug.print statements added for testing -- about 30 lines of code):

Sub ProtectAll(Optional xAll As String = "All", _
Optional xProtect As String = "Yes")
' xAll: process All or Active sheet(s)
' xProtect: Yes to Protect, No to Unprotect

Dim xFrom As Integer, xTo As Integer

Debug.Print "ProtectAll Begin ScreenUpdating: ";
Application.ScreenUpdating
Application.ScreenUpdating = False
Debug.Print "ProtectAll Begin ScreenUpdating: ";
Application.ScreenUpdating

If xAll = "All" Then
xFrom% = 1
xTo% = Sheets.Count
Else
xFrom% = ActiveSheet.Index
xTo% = xFrom%
End If

For ii% = xFrom% To xTo%
If xProtect = "No" Then
Sheets(ii%).Unprotect
Else
Sheets(ii%).Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True, _
AllowFormattingCells:=True
Sheets(ii%).EnableSelection = xlUnlockedCells
End If
Next ii%

Debug.Print "ProtectAll End ScreenUpdating: ";
Application.ScreenUpdating
Application.ScreenUpdating = True
Debug.Print "ProtectAll End ScreenUpdating: ";
Application.ScreenUpdating

End Sub

When my new function / conditional formatting is active, only the first
three debug.print statements are executed. In fact, as near as I can
determine, VBA returns to Excel as though it completed normally, even
though it has 'bailed' somewhere 'in the middle' of the process.

I'm hoping that some of you "in this room" will have pointers for me on
what to look for and / or how to proceed with testing.

(My new function is approximately 150 lines of code ... I'm hesitant to
just throw it out here without invitation.)


--
Clif