Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2012
    Posts
    5

    Unanswered: 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
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    which line throws the fault?
    is this an Access problem or an Excel problem?

    I'd guess its an Excel problem and the fault is on
    Code:
    .IconSet = ActiveWorkbook.IconSets(xl3TrafficLights1)
    it probably should read
    Code:
    .IconSet = ActiveWorkbook.IconSets("xl3TrafficLights1")
    Im guessign you haven't declared 'option explicit' as the first line of the code module to force declaration of varaibles, although to be honest Im not certain Excel VBA requires that
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Like the Kawasi/Triumph/Norton maven, this appears to me to be Excel code, which is to say that it certainly doesn't look like any Access VBA that I've ever seen! Which begs the question "Why didn't you post it on an Excel forum?" While they both have the same ancestors, VBA for Access is not VBA for Excel!

    Here's the URL for our very own Excel site:

    Microsoft Excel - dBforums

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Aug 2012
    Posts
    5
    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

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK so its VBA running inside Access.
    Which line throws the error?
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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