Conditional Formatting

[riaarora]

Hi


I'm trying to apply conditonal formatting with TrafficLights using below access VBA but getting error "object doesn't support this property or method". I'm new to Access VBA please let me know what I'm doing wrong.

Sub setRAGStatusFontColorIcon(strRange As String)
    Dim objISet As Excel.IconSetCondition
    With objXLApp
        .FormatConditions.Delete
        Set objISet = .FormatConditions.AddIconSetCondition
        .FormatConditions(.FormatConditions.count).SetFirstPriority
        With objISet
            .ReverseOrder = True
            .ShowIconOnly = False
            .IconSet = ActiveWorkbook.IconSets(xl3TrafficLights1)
            With .IconCriteria(2)
                .Type = xlConditionValueFormula
                .Value = 1
                .Operator = 5
            End With
            With .IconCriteria(3)
                .Type = xlConditionValueFormula
                .Value = 0.9
                .Operator = 5
            End With
        End With
    End With
End Sub

[June7]

You have posted VBA/Excel code in an Access forum. You might have better luck posting to an Excel forum.

What is 'Traffic Lights'?

Why are you setting conditional format with code and not directly with the spreadsheet?

Exactly which line is triggering the error?

Want to provide the files for analysis? Follow instructions at bottom of my post.

[John_G]

1) what is objXLapp? Is it defined and set somewhere outside that Sub? If no, this is the error - Access does not know what objXLapp is.

2) which line of code is generating the error, as June 7 pointed out? Are you able to enter the debug mode when the error occurs?

3) you have strRange as a parameter to the Sub, but you never use it - is that intentional?

Do you have Option Explicit as one of the options at the top of your code module? If not, I suggest you add it and then re-compile your code - you'd be suprised at the errors that might reveal.

HTH

John

[riaarora]

There is no compilation error. Please find below the code

Variables outside the sub

Option Compare Database
Option Explicit

Public appAccess As Access.Application
'Dim objXLApp As Object 'Excel.Application
Dim objXLApp As Excel.Application
'Dim objXLWorkbook As Object 'Excel.Workbook
Dim objXLWorkbook As Excel.Workbook
'Dim objXLSheet As Object 'Excel.Worksheet
Dim objXLSheet As Excel.Worksheet

There is another piece of code to create the Excel file and populate the data from ACCESS to Excel, that is working fine. After populating the numbers from Access to Excel I'm formatting using ACCESS VBA.

Sub setRAGStatusFontColorIcon(strRange As String)
    Dim objISet As Excel.IconSetCondition
    With objXLApp
        Set objISet = .FormatConditions.AddIconSetCondition              '---> ERROR
        .FormatConditions(.FormatConditions.count).SetFirstPriority      '---> ERROR
        With objISet
            .ReverseOrder = True
            .ShowIconOnly = False
            .IconSet = ActiveWorkbook.IconSets(xl3TrafficLights1)
            With .IconCriteria(2)
                .Type = xlConditionValueFormula
                .Value = 1
                .Operator = 5
            End With
            With .IconCriteria(3)
                .Type = xlConditionValueFormula
                .Value = 0.9
                .Operator = 5
            End With
        End With
    End With

[pbaldy]

What version of Excel? I've never used it, but according to Help AddIconSetCondition came in with 2007. Your profile says Access 2003, so if your Excel version is also 2003, that wouldn't be available.

[June7]

I almost understand what this code is doing. Perhaps you could offer more info.

According to
http://social.msdn.microsoft.com/For...f-19360dc765f4
http://office.microsoft.com/en-us/ex...080551788.aspx
FormatConditions.AddIconSetCondition is method of a Range, not application object.

[riaarora]

I'm using Access 2007 and Excel 2007