Excel.AddIconSetCondition Method in MS Excel to Add an Icon Set For a Given Range of Values in VB.NET

In this article we are going to explain how to add an icon set for given range of values in Excel 2010 workbook.
  • 4504

Introduction

In this article we are going to explain how to add an icon set for given range of values in Excel 2010 workbook.

Microsoft Office 2010 offer some powerful tools, using this tools you can create application. Using Microsoft Visual Basic for Applications (VBA) you can create your own application according to your need. These application can performer some specific task.

For creating application we can use

  • VBA host of Excel 2010
  • VBA host of PowerPoint 201ss0
  • VBA host of Word 2010

NOTE : OneNote 2010 is not a VBA host.

Code that we use in this application are given below

Sub TestAddIconSet()
    Dim a As 
Integer
    Dim b As Range

    ' b define the range

    For a = 1 To 20

        ' Set up ranges

        b = SetupRange(a)
        Select Case a
            Case 1
                SetUpIconSet(b, xl3Arrows)
            Case 2
                SetUpIconSet(b, xl3ArrowsGray)
            Case 3
                SetUpIconSet(b, xl3Flags)
            Case 4
                SetUpIconSet(b, xl3Signs)
            Case 5
                SetUpIconSet(b, xl3Stars)
            Case 6
                SetUpIconSet(b, xl3Symbols)
            Case 7
                SetUpIconSet(b, xl3Symbols2)
            Case 8
                SetUpIconSet(b, xl3TrafficLights1)
            Case 9
                SetUpIconSet(b, xl3TrafficLights2)
            Case 10
                SetUpIconSet(b, xl3Triangles)
            Case 11
                SetUpIconSet(b, xl4Arrows)
            Case 12
                
' Reverse the order on this one:
                SetUpIconSet(b, xl4ArrowsGray, True)
            Case 13
                SetUpIconSet(b, xl4CRV)
            Case 14
                SetUpIconSet(b, xl4RedToBlack)
            Case 15
                SetUpIconSet(b, xl4TrafficLights)
            Case 16
                SetUpIconSet(b, xl5Arrows)
            Case 17
                
' Reverse the order on this one:
                SetUpIconSet(b, xl5ArrowsGray, True)
            Case 18
                SetUpIconSet(b, xl5Boxes)
            Case 19
                SetUpIconSet(b, xl5CRV)
            Case 20                SetUpIconSet(b, xl5Quarters)
        End 
Select
    Next a

End
 Sub

Function SetupRange(col As IntegerAs Range

    ' Set up ranges, filled with numbers from 1 to 10.

    b = Range(Cells(1, col), Cells(10, col))
    Dim c As Range
    c = Cells(1, col)
    c.Value = 1
    Dim d As Range
    d = Cells(2, col)
    d.Value = 2

    Range(c, d).AutoFill(Destination:=b)
    SetupRange = b

End Function

Sub SetUpIconSet(b As Range, iconSet As XlIconSet, Optional ReverseOrder As Boolean = False)

    ' Set up an icon set for the supplied range.

    b.FormatConditions.Delete()
    Dim isc As IconSetCondition
    isc = b.FormatConditions.AddIconSetCondition
    With isc

        ' If specified, show the icons in the reverse ordering:

        .ReverseOrder = ReverseOrder
        .ShowIconOnly = 
False

        ' Select the requested icon set:

        .iconSet = ActiveWorkbook.IconSets(iconSet)
    End 
With

End
 Sub

Step for creating Application

Step 1 : Start MS Excel 2010 :

Clipboard01.jpg

 

Clipboard2.jpg
 

Step 2 : Using Alt + F11 Key Start Visual Basic for Applications (VBA) Window :

Clipboard03.jpg
 

Step 3 : Select on Sheet 1 :

Clipboard14.jpg
 

Step 4 : Right Click on Sheet 1 and select View Code :

5.jpg
 

Step 5 : Write Code in Visual Basic for Applications (VBA) Window :

6.jpg
 

Step 6 : Run Application using F5 
 

Step 7 : Macros window will open, Select Macros name and click on Run Button :

7.jpg
 

Step 8 : Output of Application :

8.jpg

Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.