Where to begin?

It installs OK under XL97SR-2 under Windows NT4SP6.

I created the following conditional formats with cell A1 active.

=A1<0
background color pink (leftmost color in bottom row of square block of
colors in Patterns tab

=A1<1
background color orange (next color to the right of pink in Pattern
tab)

=A1<10
background color pale yellow (next color to the right of orange in
Pattern tab)

=A1<100
background color pale green (next color to the right of pale yellow in
Pattern tab)

=A1<1000
background color pale blue (next color to the right of pale green in
Pattern tab)

It works for static values in A1, but you know me, I like to torture
test. It gives sporadically random wrong results for the volatile
formula

=1/RAND()^2-5

You're using x.PasteSpecial Paste:=xlPasteFormats, so your code suffers
from what I consider a bug in Excel: pasting formats or comments into a
range triggers recalculation of that range. That should be intended
functionality when pasting formulas or values, but unhelpful when
pasting formats. FWLIW, 123 releases 5 and 97 and OOo Calc also share
this 'functionality'.

The problem is that this differs from Excel's built-in conditional
formatting functionality. Not good. Looks like you'll either need to
disclaim your add-in working with cells containing volatile functions,
or use the slow but sure way of setting conditional formats one at a
time rather than using copy & paste. This is at variance to some
private e-mail correspondence between Frank and me, but I wasn't aware
of this little headache until recently.


Next, either the copy or paste format function doesn't work. If I
select the A1 cell formatted as above, run the menu command

xld Tools CF Plus Special Functions Copy Format

select A2, run the menu command

xld Tools CF Plus Special Functions Paste Format

then run the menu command

xld Tools CF Plus Launch CF+

I see no conditions in the dialog for cell A2.


Next, and REALLY ANNOYING! I return to cell A1 and run the menu
command

xld Tools CF Plus Special Functions Highlight ranges

There's garbage now appearing as a drawn object in cell A1 obscuring
the contents of cell A1. This isn't the annoyance for me. If I click on
A1, I get a dialog showing its conditions. This also isn't the annoyance
for me (though why there are both OK and Cancel buttons in this dialog
is a mystery). No, what's annoying about this is that if I select some
other cell then click on A1, the dialog appears again, but after
dismissing the dialog the cell pointer is returned to it's starting
point rather than staying in cell A1. On the other hand, if I use
cursor keys to return the cell pointer to A1, I don't get the dialog
and I stay in cell A1. Maybe this is specific to XL97, but REALLY
ANNOYING that *clicking* on cell A1 won't leave the cell pointer at
A1.

Also, if you can display formatting in the preview box of the
Conditional Format Plus - Add dialog, you should display something
similar when range highlighting is enabled. The MsgBox dialog you're
currently showing isn't particularly informative.


--
hgrove
------------------------------------------------------------------------
hgrove's Profile: http://www.excelforum.com/member.php...o&userid=11432
View this thread: http://www.excelforum.com/showthread...hreadid=276025

Hi Harlan
Where to begin?

first I was afraid for what might follow :-)


It installs OK under XL97SR-2 under Windows NT4SP6.
Good (Bob has added a platform table to show which versions work ok)


I created the following conditional formats with cell A1 active.
[...]

It works for static values in A1, but you know me, I like to torture
test. It gives sporadically random wrong results for the volatile
formula

=1/RAND()^2-5

you're not fair :-) - who want to conditioanl format such a formua
<vbg


You're using x.PasteSpecial Paste:=xlPasteFormats, so your code
suffers
from what I consider a bug in Excel: pasting formats or comments into
a
range triggers recalculation of that range. That should be intended
functionality when pasting formulas or values, but unhelpful when
pasting formats. FWLIW, 123 releases 5 and 97 and OOo Calc also share
this 'functionality'.

thanks for this specific error explanation. But looking at the code I
would suspect that it is not this code part but the resetting of the
application.calculate mode to automatic.
Just executing the two statements
application.calculate=xlCalculationManual
application.calculate=xlCalculationAutomatic

will re-calculate volatile functions.


The problem is that this differs from Excel's built-in conditional
formatting functionality. Not good. Looks like you'll either need to
disclaim your add-in working with cells containing volatile
functions,
or use the slow but sure way of setting conditional formats one at a
time rather than using copy & paste. This is at variance to some
private e-mail correspondence between Frank and me, but I wasn't
aware
of this little headache until recently.

As above don't think this would help. So probably a statement in the
help / webpage that it does not work with volatile functions such as
RAND()


Next, either the copy or paste format function doesn't work. If I
select the A1 cell formatted as above, run the menu command
[...]

Yes, a bug. Wrong check procedure at the beginning due to a last minute
change. will work in the next update. Thanks for that spot.
We will also add a note that for complicated conditions copying these
format copying may not work (esp. if you're using relative references
in the condition)


Next, and REALLY ANNOYING! I return to cell A1 and run the menu
command

xld Tools CF Plus Special Functions Highlight ranges

There's garbage now appearing as a drawn object in cell A1 obscuring
the contents of cell A1. This isn't the annoyance for me. If I click
on
A1, I get a dialog showing its conditions. This also isn't the
annoyance
for me (though why there are both OK and Cancel buttons in this
dialog
is a mystery).

Will remove the Cancel button :-)
The 'garbage' is due to using a shape to indicate the used CF cells.
This feature is only a 'nice to have' to show which cells have a CF
applied.


No, what's annoying about this is that if I select some
other cell then click on A1, the dialog appears again, but after
dismissing the dialog the cell pointer is returned to it's starting
point rather than staying in cell A1. On the other hand, if I use
cursor keys to return the cell pointer to A1, I don't get the dialog
and I stay in cell A1. Maybe this is specific to XL97, but REALLY
ANNOYING that *clicking* on cell A1 won't leave the cell pointer at
A1.

Works as designed :-) and also the reason why the cursor keys don't
trigger the message box is also due to using the shape object
But still valid points from your side and depending on other feedback
we may change this behaviour


Also, if you can display formatting in the preview box of the
Conditional Format Plus - Add dialog, you should display something
similar when range highlighting is enabled. The MsgBox dialog you're
currently showing isn't particularly informative

Good idea. I'll check how much effort this would be

Thanks again for your feedback

Frank

Frank Kabel wrote...
...
thanks for this specific error explanation. But looking at the code I
would suspect
that it is not this code part but the resetting of the
application.calculate mode to
automatic. Just executing the two statements

application.calculate=xlCalculationManual
application.calculate=xlCalculationAutomatic

will re-calculate volatile functions.

Perhaps they do, but do you think I don't know what I'm talking about?
Start off with a new workbook, for good measure, UNINSTALL CF Plus,
enter =RAND() in A1, then format B1 with a red background, copy B1 and
paste special as format onto A1. What happens to the value in cell A1?

So, you just didn't want to test my assertion?

Next, either the copy or paste format function doesn't work. If I
select the A1 cell formatted as above, run the menu command

Yes, a bug. Wrong check procedure at the beginning due to a last
minute
change. will work in the next update. Thanks for that spot. We will
also add a
note that for complicated conditions copying these format copying may
not work
(esp. if you're using relative references in the condition)
...

So you broke a different fundamental programming rule: NEVER, repeat
NEVER! make last minute changes before releasing any code. The odds are
phenominally high you'll screw things up. I'll let you figure out the
corrollaries with respect to feature locks.

The 'garbage' is due to using a shape to indicate the used CF cells.
This feature
is only a 'nice to have' to show which cells have a CF applied.

OK, suggestion for long-term feature change: make it an optional
setting how to

No, what's annoying about this is that if I select some
other cell then click on A1, the dialog appears again, but after
dismissing the dialog the cell pointer is returned to it's starting
point rather than staying in cell A1. On the other hand, if I use
cursor keys to return the cell pointer to A1, I don't get the dialog
and I stay in cell A1. Maybe this is specific to XL97, but REALLY
ANNOYING that *clicking* on cell A1 won't leave the cell pointer at
A1.

Works as designed :-) and also the reason why the cursor keys don't
trigger the
message box is also due to using the shape object But still valid
points from
your side and depending on other feedback we may change this
behaviour
...

Or make it optional.


--
hgrove
------------------------------------------------------------------------
hgrove's Profile: http://www.excelforum.com/member.php...o&userid=11432
View this thread: http://www.excelforum.com/showthread...hreadid=276025

Hi Harlan
Frank Kabel wrote...
..
thanks for this specific error explanation. But looking at the code
I
would suspect
that it is not this code part but the resetting of the
application.calculate mode to
automatic. Just executing the two statements

application.calculate=xlCalculationManual
application.calculate=xlCalculationAutomatic

will re-calculate volatile functions.

Perhaps they do, but do you think I don't know what I'm talking
about?
Start off with a new workbook, for good measure, UNINSTALL CF Plus,
enter =RAND() in A1, then format B1 with a red background, copy B1
and
paste special as format onto A1. What happens to the value in cell
A1?

So, you just didn't want to test my assertion?

Of course I tested it!. I stepped through the code and the pastespecial
calls did not alter the value of cell A1. I would suspect as I disabled
automatic calculation at the beginning the cell content is not changed.
Only after enabling automatic calculation again cell A1 changes.
I don't doubt that pasting formats also trigger a re-calculation and
I've tested it using the same approach you outlined above BEFORE
answering your post but even if I would remove this code parts from the
addin the re-setting of the calculation mode alone would trigger a
re-calculation (and I tested this as well).



[...]
So you broke a different fundamental programming rule: NEVER, repeat
NEVER! make last minute changes before releasing any code. The odds
are
phenominally high you'll screw things up. I'll let you figure out the
corrollaries with respect to feature locks.

:-) This change was a required bug fix and of course tested but as it
is obvious not for all cases. And as we don't have an automatic
regression tool setup this unfortunately could hapen.
I agree with you in general on this though. Call this a hotfix which
has not been tested enough on our side!


The 'garbage' is due to using a shape to indicate the used CF cells.
This feature
is only a 'nice to have' to show which cells have a CF applied.

OK, suggestion for long-term feature change: make it an optional
setting how to

O.K. :-)

Frank