Excel2003 Determine styleS applied to a cell

[ChrisGreaves]

The attached workbook has three styles applied to the cell B4.
(1) The system supplied "Comma" style with two decimal points
(2) The user supplied "Input" style which supports a pale blue background
(3) The user supplied "Border" style which supports a solid border.

The three styles were applied one by one to that cell.

I cannot find a way, in VBA, to determine a set of styles that are applied to a cell. It seems to me that only the most recently applied style shows up in VBA.

Is there a way to enumerate or list all the styles currently in force on a cell?

I can dream up various work-arounds if I wanted to duplicate the formatting to a separate range:-
Edit Copy (B4) and then Edit+paste Special+formaTs somewhere else.
Add to that "defining a transient style" based on the complete formatting characteristics, and so on.

Ideally VBA would create a string array of style names ("Comma", "Input", "Border") and pass the array to a function whose purpose was to apply an array of style names to a range.

Thanks
Chris

[HansV]

Uh - when I open your workbook in Excel 2019, cell B4 has no fill color and no borders...

S0263.png

[ChrisGreaves]

Uh - when I open your workbook in Excel 2019, cell B4 has no fill color and no borders...

Odd.
Probably Human Error; or else it might be my fault.
I have checked the Book1.xls and re-attached an updated copy to this reply, as well as the PNG I intended to attach (with a blue border) and a PNG from a couple of minutes ago.
I apologize for sending you two problems (grin)
Thanks
Chris

[HansV]

That's different - I now see the background color and the thick border. And the custom styles exist - they didn't in the first version.

It looks like Excel doesn't handle multiple styles well. When you apply a new style, the previously applied formatting loses its connection to its style(s). So I fear that it is impossible to determine with certainty which styles have been applied.

You might loop through all styles, and check whether the cell formatting matches that of each style in the loop, but
a) This would be very tedious
b) Even if the formatting matches, it doesn't guarantee that the style has actually been applied to the cell, since styles can 'overlap'.

[HansV]

Probably Human Error; or else it might be my fault.

[ChrisGreaves]

You might loop through all styles, and check whether the cell formatting matches that of each style in the loop, but
a) This would be very tedious
b) Even if the formatting matches, it doesn't guarantee that the style has actually been applied to the cell, since styles can 'overlap'.

Thanks for the confirmation, Hans.
I am sure that there are many ways to fake the recognition of the style, although I do not really like interrogating cell format at all, since I could avoid styles and format a cell as "Pale Blue Background", "Comma(2)" and "Thick Border", but that would not necessarily mean that I had applied the defined three styles to the cell.
Indeed I might have purposely used local formatting on a master cell so that the master cell would not change if the user changed the definition of any one of the three styles.

As an aside, I rather like both the Word way of implementing styles, and the Excel way.

In particular I like that I can apply my styINPUT style (pale blue background) independently of the numeric, alignment etc characteristics. Pale Blue then tells the user "You should key in data in this cell", regardless of the number format. I can use numeric style styFraction where the carpenter inputs dimension data , and numeric styCost where the costing clerk inputs data, keeping both clerks happy!

Back in the day when I was paid to teach, the two different mechanisms (Word/Excel) improved my revenue stream a little bit ("Oh No! The way that styles work in Excel is QUITE different from the way they work in Word; we definitely need a separate course for styles in Excel ...".

Thanks again for the confirmation.
Chris