Results 1 to 4 of 4

Thread: Conditional Formatting

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jan 2012
    Posts
    71

    Conditional Formatting

    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.

    Code:
    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

  2. #2
    Frenzied Member
    Join Date
    Jan 2009
    Location
    Watch Window(Shift+f9)
    Posts
    1,879

    Re: Conditional Formatting

    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.
    it seems there is some variable issue . did you cross check .better write the option explicit and compile again where you declare variable. just one line up

    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.
    Code:
    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

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Jan 2012
    Posts
    71

    Re: Conditional Formatting

    There is no compilation error. Please find below the code

    Variables outside the sub

    Code:
    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.

    Code:
    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

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Conditional Formatting

    Thread moved from the 'Database Development' forum (which is for questions about tables/SQL/etc) to the 'Office Development/VBA' forum (which is where other Access/Excel based questions belong)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width