OWC Row Coloring - Thanks Mike/Dan

[Dip]

Hi Mike,
Thanks for your suggestion but I am afraid Dan is somewhat quite right.
I just have tested. OWC Methods and Property does not allow you to
change the colour of particular row rather than Total and Subtotal but
you can change the Caption of it!! This is really stupid to me since my
most end users are Accountant and they want to see all the account code
and it's totals. I have created these totals as Calculated Member in
Analysis Service because they do not want to see the Totals that appear
in a "Drilldown" hierarchical mode. Then there is a need to colour it to
distinguish.

I think, at this stage I would change the captions and probably provide
a button to export the view into Excel with a pre-defined cell colours
etc. I just can't say to them "I can’t do it" or "Microsoft can’t do
it". Then it's really defeats the purpose of providing consulting and
charging big bucks:~)

Anyway, thanks to Mike and Dan. If you find any work-around, please let
me know. This would be a great help again.
Cheers.
Dip


From: Mike Edgar
Date: Yesterday 20:56
Subject: Re: Very Urgent: Coloring for a Particular Row - Please Help

-----------------------------------------------------------------------
----------

Dip, I'm sorry, i haven't tested this code, but i reckon it would do
what
you want...
you'll obviously need to change it to select the right range for your
objects and correct language syntax,

// set a range object to the row on the pivotdata that you want
to colorise,
rngCurrentRow = ptable.PivotData.Range(topleft, bottomright)
//Set the interior color of the row.
rngCurrentRow.Interior.Color = "Blue"

basically this acts on a range, so it should work on any range
that you can
define...
you could try looking at the EntireRow property in the
OWCVBA10.chm file but
again it uses a range, just lets you select a row because it
contains a cell
you are already looking at ...

Mike

 

[Dan Ricker]

This is one of the times that the separation of the
SpreadSheet and the PivotTable into separate controls is
sometimes problematic.

For OWC10/11 there is the possibility of using the two
controls in conjunction to get a desired result but the
complexity of the solution greatly increases.

The Both the PivotTable SpreadSheet controls have an
HTMLData Property.

The statement: SpreadSheet.HTMLData = PivotTable.HTMLData

basically copies the current PivotTable into the
SpreadSheet (sort of like a [Copy]/[Paste Values] in
Excel). The SpreadSheet does not have Pivot like
interactivity, but the SpreadSheet does have individual
cell formatting.

This may or may not be of use to you... but I thougth I'd
relay the information.

Thx
Dan

 

[Dip]

Thanks Dan,
That's exactly what I doing now. Just dumping OWC Pivot Table into
regular Excel with some pre-defined disciplines such as Colour, Width
etc. Yes you right, it wouldn't have the nice interactivity though. But
what else I can do. By they way, if I stuck with Excel rules I might
shoot you another posting. I am not very good at Excel. Hope you would
rescue me :~))
Thanks Man! Nice week end.
Cheers.
Dip