Joel Krist

Hi,

I'm trying to do some icon set conditional formatting in Excel 2007. I'm doing this via interop from VB.NET. I have a question regarding setting the properties of an icon criteria. The docs on MSDN (http://msdn2.microsoft.com/en-us/library/bb224560.aspx) say that the IconCriterion.Type property is read-only and sure enough if I try to set it I get an exception (HRESULT: 0x800A03EC). Same thing when I try to set the IconCriterion.Value property. However, I've seen other posts showing code that sets the Type property.

My question is, how do I set a criterion's type to be xlConditionValueNumber and set it's value to be what I want

Following is a bit of the code I'm trying to use.

Thanks for any help,

Joel

' Create an icon set conditional format for the created sample data range.

Dim cfIconSet As IconSetCondition = targetSheet.Range("F1", "F10").FormatConditions.AddIconSetCondition()

' Change the icon set to a 5-arrow icon set.

cfIconSet.IconSet = newWorkbook.IconSets(XlIconSet.xl5Arrows)

' Set the criteria for the first arrow in the set.

cfIconSet.IconCriteria(1).Type = XlConditionValueTypes.xlConditionValueNumber ' Fails

cfIconSet.IconCriteria(1).Value = 0 ' Fails

cfIconSet.IconCriteria(1).Operator = XlFormatConditionOperator.xlGreaterEqual ' Succeeds



Re: Visual Studio Tools for Office Setting IconCriterion.Type property

Dennis Wallentin

Hi John,

See if the following example will get You started: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1196854&SiteID=1






Re: Visual Studio Tools for Office Setting IconCriterion.Type property

Joel Krist

Thanks Dennis,

I did see that post and my code is doing basically the same thing. For some reason however, when I attempt to set the Type or Value properties of the IconCriterion I get an exception.

- Joel





Re: Visual Studio Tools for Office Setting IconCriterion.Type property

Dennis Wallentin

Joel,

My apologize for not using Your real name.

What about the following snipp VB code

Dim xlapp As New Excel.Application

Dim xlBook As Excel.Workbook

With xlapp

.UserControl = True

.Visible = True

xlBook = .Workbooks.Add()

End With

Dim xlSheet As Excel.Worksheet = CType(xlapp.ActiveSheet, Excel.Worksheet)

Dim xlRange As Excel.Range = xlSheet.Range("F1:F10")

Dim xlCF As Excel.IconSetCondition = CType(xlRange.FormatConditions.AddIconSetCondition, Excel.IconSetCondition)

xlCF.IconSet = xlBook.IconSets(Excel.XlIconSet.xl5Arrows)

With xlCF

.IconCriteria(2).Value = 45

.IconCriteria(3).Value = 35

End With

Which work as expected on my machine.






Re: Visual Studio Tools for Office Setting IconCriterion.Type property

Joel Krist

Hi Dennis,

No worries on the name and thanks for the help. Actually, the problem I was running into had to do with the fact that the Type and Value properties of IconCriteria(1) are read-only.

I guess that makes sense. Excel apparently treats IconCriteria(1) as a special case that handles values that are not handled by the other icon criteria. The icon set formatting object model docs don't talk about this and are actually incorrect in that they show sample code that sets the Type and Value properties of IconCriteria(1). Actually, if you apply icon set formatting through the UI in Excel you always specify values and types for one less than the number of icons in the selected set and Excel determines on its own the threshhold value of the remaining icon.

- Joel