CODEWITHSUNDEEP
excelvba
James Carlyle-Clarke
James Carlyle-Clarke

Reputation: 868

Strange errors reading properties of a FormatCondition

I have a FormatCondition, cf1. I do the following:

Dim WS As Worksheet
Dim fcs As FormatConditions
Dim cf1 As FormatCondition
Dim b1 As Border

Set WS = ActiveSheet
Set fcs = WS.Cells.FormatConditions
Set cf1 = fcs.item(1)
Set b1 = cf1.Borders.item(xlEdgeBottom)

Dim ls As XlLineStyle
ls = b1.LineStyle

If I then try to read the b1.LineStyle in the Immediate window or assign it to a variable (per the last line above) I get

Error 1004: 'Unable to get the LineStyle property of the Border class'

(It's the same error string in the watch list but without the error number)

I get something similar for b1.Weight

This happens whether there is a line on the border of the Conditional Format or not, which surely cannot be right. If there is indeed no Line Style for that format I would think that LineStyle should then be xlLineStyleNone, and if there is a Line Style then surely it should be readable?

Does anyone know what is going on and how I can fix it?

Please note: I've looked at the borders found and some of the other Border properties seem right (ie the Color=0 and ColorIndex is either -4142 or -4105). ThemeColor and TintAndShade are Null, and Creator is xlCreatorCode.

I found some references to issues in setting LineStyle and Weight, and those said that the sheet was locked. This is not the case here as far as I know.

ADDITION: I sometimes get a different error when trying to read b1.ThemeColor as well.

'Application-defined or object-defined error'

in the watch list, or

Run-time error 5: 'Invalid procedure call or argument'

when reading in the Immediate window. Other times it is just Null

FURTHER ADDITION: For cf1.Interior.InvertIfNegative I get

'Application-defined or object-defined error'

in the watch list, or

Run-time error 1004: 'Application-defined or object-defined error'

when trying to read in the Immediate window.

There may be others I haven't found yet.

Upvotes: 0

Views: 441

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

Actually I can reproduce the issue you describe.

The issue seems to be in Set b1 = cf1.Borders.item(xlEdgeBottom) which is correct according to the documentation FormatCondition.Borders property.

But there seems to be a bug, because the value of xlEdgeBottom is 9 (according to xlBordersIndex enumeration) and if we have a look into the debug of cf1.Borders we see that there is only item 1 … 4 (see image) where 4 seems to be the bottom border.

enter image description here

If no one else has a very good idea here I would say this looks like a Excel bug.

To workaround this you can use eg Set b1 = cf1.Borders.item(4) for the bottom border.

So the solution is …

that Set b1 = cf1.Borders.item(xlBottom) actually worked for me.

Obviously the FormatCondition.Borders doesn't use the correct xlBordersIndex enumeration as it should do but instead uses xlTop, xlLeft, xlBottom, xlRight.

Also refer to FormatConditions border on a single edge.

But still this is not in accordance with the documentation and must be a bug.

Upvotes: 3

Related Questions