Conditional Formatting

[Bob Phillips]

This posting is to give notice of a new, free add-in that addresses a
frequently requested function within Excel, that of having more than 3
conditional formats.

The add-in, which was conceived and developed by Frank Kabel and Bob
Phillips, is in Beta release, and can be obtained at
www.xldynamic.com/source/xld.CFPlus.Download.html. Currently, there is no
documentation, but you should find it relatively simple to use, with it's
own menu, and quick launch options. The only function that we draw your
attention to is something that we call 'special conditions'. When the
Condition form is shown, in Formula mode, there is a dropdown to the right,
which, if clicked shows a list of these special conditions. These provide
simple generation of formulae for a number of condition requirements, such
as row striping, date conditions, etc.

We are announcing this product through the NGs, as a means of soliciting
participation in the testing of this Beta release. As a Beta release, we
cannot rule out that there may be some problems and some errors with the
code. We would appreciate feedback on problems and errors, but please do not
use the NGs to report these problems, use the link at the foot of the page
mentioned above. Major problems will be fixed and released as quickly as
possible, whereas minor problems or significant enhancements may be delayed
until a production release. The production release will also include a help
file.

We hope that you find this add-in useful, and easy to use.

Frank Kabel & Bob Phillips

 

[Trevor Shuttleworth]

Bob

I'm sure this will be very popular !

It's early days yet so nothing to report ... but you didn't include the link
to contact you .

Regards

Trevor

 

[Frank Kabel]

Hi Trevor
link is at the bottom of the download page
(e-mail address removed)

 

[Trevor Shuttleworth]

aahhhhh ... only got as far as "use the link at the foot of the page".
Missed the "mentioned above". C'est la vie. Je suis desolé. :-(

 

[Harlan Grove]

This posting is to give notice of a new, free add-in that addresses a
frequently requested function within Excel, that of having more than 3
conditional formats.

....

FWIW, McAfee Virus Scan suspends execution of the initialization code. Bob &
Frank, if your add-in triggers anti-virus software, you're not going to get
many business users to use it.

How am I supposed to use this? Installing the add-in under XL2K, there's no
new menu entry in either Format or Tools menus. Since you provide no docs,
you might want to add a sentence or two to the web page providing basic
instructions for accessing the wonderful functionality. FWIW, I got a
runtime error when installing it using Tools > Add-ins under XL2K,
specifically, Run-time error 5: Invalid procedure call or argument.

Y'all didn't violate one or another of the very basic rules of Excel
development, did ya? Either state any version dependencies (e.g., only works
with XL2002 or higher), or write & test in the oldest targetted Excel
version (XL97? XL2K? XL2002?).

 

[Frank Kabel]

Hi Harlan

...

FWIW, McAfee Virus Scan suspends execution of the initialization
code. Bob & Frank, if your add-in triggers anti-virus software,
you're not going to get many business users to use it.

Agreed and that's the reason why I asked for the virus scanner which
causes an error to see what the reason for this warning is

How am I supposed to use this? Installing the add-in under XL2K,
there's no new menu entry in either Format or Tools menus. Since you
provide no docs, you might want to add a sentence or two to the web
page providing basic instructions for accessing the wonderful
functionality. FWIW, I got a runtime error when installing it using
Tools > Add-ins under XL2K, specifically, Run-time error 5: Invalid
procedure call or argument.

There's a new top-level menu (called 'XLD'). We started putting this
new functionality under the Format menu but Excel has a bug which
prevents some functionality in this case.
The error is interesting as it was tested under XL2K. We had the same
error report for Windows Me and are currently looking at this

Y'all didn't violate one or another of the very basic rules of Excel
development, did ya? Either state any version dependencies (e.g.,
only works with XL2002 or higher), or write & test in the oldest
targetted Excel version (XL97? XL2K? XL2002?).

Bob just is in the progress to add the tested platforms. It should run
in all Excel versions starting with Excel 97 under any Windows
platform. It won't run under a MAC environment though

Frank

 

[hgrove]

Frank Kabel wrote...
...

Agreed and that's the reason why I asked for the virus scanner which
causes an error to see what the reason for this warning is

...

That info I'll have to send later when I get home.

The error is interesting as it was tested under XL2K. We had the sam error
report for Windows Me and are currently looking at this

...

I should have mentioned that I was running this under Windows Me. Od
that this should have OS dependence

 

[Frank Kabel]

Hi Harlan

Frank Kabel wrote...
..
That info I'll have to send later when I get home.

Many thanks for that. As update it seems to be the registry read
procedure and for F-Secure I've contacted their technical support.
Seems to happen as well for some versions of McAffee scan
FWIW ASAP utilities raises for another user the same error

same
..

I should have mentioned that I was running this under Windows Me. Odd
that this should have OS dependence.

Our current guess is that it may be the usage of the Windows Scripting
Host for reading the registry (WSH should be installed on all PCs
having IE 5.0 or higher on it).

Frank

 

[hgrove]

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 o
colors in Patterns tab

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

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

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

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

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

=1/RAND()^2-5

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

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


Next, either the copy or paste format function doesn't work. If
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 men
command

xld Tools > CF Plus > Special Functions > Highlight ranges

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

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

 

[Frank Kabel]

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

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

 

[hgrove]

Frank Kabel wrote...
...

thanks for this specific error explanation. But looking at the code would suspect
that it is not this code part but the resetting of th 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 an
paste special as format onto A1. What happens to the value in cell A1?

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

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

...

So you broke a different fundamental programming rule: NEVER, repea
NEVER! make last minute changes before releasing any code. The odds ar
phenominally high you'll screw things up. I'll let you figure out th
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 optiona
setting how to

Works as designed and also the reason why the cursor keys don' trigger the
message box is also due to using the shape object But still vali points from
your side and depending on other feedback we may change thi

behaviour
...

Or make it optional