locked
Trying to Set the .Interior.Pattern Property Raises Run-time Error RRS feed

  • Question

  • I am attempting to change the value and formatting of a cell using the Worksheet_BeforeDoubleClick event. I am getting Run-time error '1004': Application-defined or object-defined error on any/all of the .Interior lines. The routine successfully sets the Value property so I don't understand why it can't also set the .Interior.Pattern property. I tried eliminating the With statement and that didn't work. I tried using .cell reference and that didn't work. Any help anyone can offer is much appreciated.

    Thanks in advance.

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    Dim lLastRow As Long
    Dim lLastColumn As Long
    
    With Me
        lLastRow = .Cells(65000, 1).End(xlUp).Row
        lLastColumn = .Cells(1, 255).End(xlToLeft).Column
    
        'Select for batch
        If Target.Row <= lLastRow And Target.Column = 6 Then
            If Target.Value = "Select" Then
                With Target
                    .Value = "Run"
                    .Interior.Pattern = xlSolid
                    .Interior.PatternColorIndex = xlAutomatic
                    .Interior.ThemeColor = xlThemeColorAccent6
                    .Interior.TintAndShade = 0.599993896298105
                    .Interior.PatternTintAndShade = 0
                    .HorizontalAlignment = xlCenter
                End With
            Else
                With Target
                    .Value = "Select"
                    .Interior.Pattern = xlSolid
                    .Interior.PatternColorIndex = xlAutomatic
                    .Interior.ThemeColor = xlThemeColorDark1
                    .Interior.TintAndShade = -4.99893185216834E-02
                    .Interior.PatternTintAndShade = 0
                    .HorizontalAlignment = xlCenter
                End With
            End If
        End If 
    
    End With
        
    Cancel = True
    
    End Sub

    Wednesday, August 19, 2020 4:14 PM

Answers

  • EDIT: ANSWERED
    I just figured this out. The sheet was protected and for some reason Excel will let you update the .Value property but not the .Interior.Pattern or .HorizontalAlignment properties on a protected sheet.

    I added .UnProtect at the beginning of the With Me and .Protect at the end of the With Me and the code runs fine.

    Leaving this here in case someone else encounters a similar issue.

    Thanks to anyone who looked at this.

    END EDIT

    I am using Office 365.

    In Excel, File -> Account -> About Excel returns "Microsoft Excel for Microsoft 365 MSO (16.0.13029.20342) 64-bit."

    I'm not terribly surprised that you couldn't reproduce the error as I have used a very similar method in other worksheets without encountering this issue. My previous code was edited to what I'm pretty sure are all the pertinent parts but for the sake of being thorough I am posting the unedited code. It includes calls to other subroutines but all of those subroutines run fine. I will also post the code from the other worksheet that uses a nearly identical method and works just fine. I tried making the problem code match the working code by eliminating the With statements but that did not resolve the problem.

    Is it likely or even possible that my Excel file has become corrupted in some way? I truly appreciate everyone who takes the time to look at this. You're the real heroes.

    If I uncomment any commented line in the "'Select for batch" section at the end of this Private Sub I get the runtime error:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    Dim lTargetRow As Long
    Dim lLastRow As Long
    Dim lLastColumn As Long
    Dim sQueryID As String
    
    Dim rTarget As Range
    
    
    With Me
        lTargetRow = Target.Row
        lLastRow = .Cells(65000, 1).End(xlUp).Row
        lLastColumn = .Cells(20, 255).End(xlToLeft).Column
    
        'run query
        If Target.Row <= lLastRow And Target.Column = 3 Then
            sQueryID = .Cells(Target.Row, 1).Value
            RunQuery (sQueryID)
        End If
    
        'retrieve query
        If Target.Row <= lLastRow And Target.Column = 4 Then
            sQueryID = .Cells(Target.Row, 1).Value
            Call RetrieveQuery(sQueryID)
        End If
        
        'Delete Query
        If Target.Row <= lLastRow And Target.Column = 5 Then
            sQueryID = .Cells(Target.Row, 1).Value
            Call DeleteQuery(sQueryID, lTargetRow)
        End If
        
        'Select for batch
        If Target.Row <= lLastRow And Target.Column = 6 Then
            If Target.Value = "Select" Then
                With Target
                    .Value = "Run"
    '                .Interior.Pattern = xlSolid
    '                .Interior.PatternColorIndex = xlAutomatic
    '                .Interior.ThemeColor = xlThemeColorAccent6
    '                .Interior.TintAndShade = 0.599993896298105
    '                .Interior.PatternTintAndShade = 0
    '                .HorizontalAlignment = xlCenter
                End With
            Else
                With Target
                    .Value = "Select"
    '                .Interior.Pattern = xlPatternSolid
    '                .Interior.PatternColorIndex = xlAutomatic
    '                .Interior.ThemeColor = xlThemeColorDark1
    '                .Interior.TintAndShade = -4.99893185216834E-02
    '                .Interior.PatternTintAndShade = 0
    '                .HorizontalAlignment = xlCenter
                End With
            End If
        End If
        
    
    End With
        
    Cancel = True
    
    End Sub
    


    This Private Sub in a different Worksheet runs fine:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    Dim lLastRow As Long
    Dim lLastColumn As Long
    Dim rngData As Range
    
    With Me
        
        lLastRow = .Cells(65000, 1).End(xlUp).Row
        lLastColumn = .Cells(20, 255).End(xlToLeft).Column
    
        If Target.Row = 20 And Target.Column < lLastColumn + 1 Then
            .Unprotect
            Set rngData = .Range(.Cells(20, 1), .Cells(lLastRow, lLastColumn))
            If Target.Interior.PatternColorIndex <> 3 Then
                rngData.Sort key1:=.Cells(1, Target.Column), order1:=xlAscending, Header:=xlYes
                Target.Interior.PatternColorIndex = 3
            Else
                rngData.Sort key1:=.Cells(20, Target.Column), order1:=xlDescending, Header:=xlYes
                Target.Interior.PatternColorIndex = 4
            End If
        End If
        
        If bDefinitionsProtected = True Then
            .Protect AllowSorting:=True, AllowFiltering:=True
            .EnableSelection = xlUnlockedCells
        End If
        
    End With
        
    Cancel = True
    
    End Sub

    Note: I'm not very concerned about what color the cells are filled with but when .Value is set to "Select" the fill should be very light grey and when the .Value is set to "Run" the fill should be a light green. The property values I am using came from a macro I recorded then deleted just to get those values.

    Note2: In the code that works, the second block, the fill colors are pretty much indistinguishable. I'm just using the  value in Target.Interior.PatternColorIndex to toggle between ascending and descending sorts.

    Thanks to anyone who read all the way down to this point.

    Thanks VERY much to anyone who can shed some light on this issue.


    • Edited by dkingston Thursday, August 20, 2020 3:35 PM Solved the problem
    • Marked as answer by dkingston Thursday, August 20, 2020 3:35 PM
    Thursday, August 20, 2020 3:25 PM

All replies

  • Under test in xl2016 and also earlier version xl2007, the code works. However, in xl2007 the Else part makes the cell grey rather than white. I am not sure but I think that is to do with theme colors in the earlier version.

    What version of Excel are you using? Is it an earlier version than xl2007.

    Just as a comment, when tested in xl2016, it appears that when value is set to "Select" that the fill color should be returned to white same as normal xl color. However, when a fill color is set to white, the grid lines disappear. If it is your intention to set it back to the default fill without hiding grid lines then the following code for the Else portion of the code.

            Else
                With Target
                    'Sets interior color back to default without losing Grid Lines
                    .Value = "Select"
                    .Interior.Pattern = xlNone
                    .Interior.TintAndShade = 0
                    .Interior.PatternTintAndShade = 0
                    .HorizontalAlignment = xlCenter
                End With
            End If

    Regards, OssieMac

    Thursday, August 20, 2020 4:52 AM
  • EDIT: ANSWERED
    I just figured this out. The sheet was protected and for some reason Excel will let you update the .Value property but not the .Interior.Pattern or .HorizontalAlignment properties on a protected sheet.

    I added .UnProtect at the beginning of the With Me and .Protect at the end of the With Me and the code runs fine.

    Leaving this here in case someone else encounters a similar issue.

    Thanks to anyone who looked at this.

    END EDIT

    I am using Office 365.

    In Excel, File -> Account -> About Excel returns "Microsoft Excel for Microsoft 365 MSO (16.0.13029.20342) 64-bit."

    I'm not terribly surprised that you couldn't reproduce the error as I have used a very similar method in other worksheets without encountering this issue. My previous code was edited to what I'm pretty sure are all the pertinent parts but for the sake of being thorough I am posting the unedited code. It includes calls to other subroutines but all of those subroutines run fine. I will also post the code from the other worksheet that uses a nearly identical method and works just fine. I tried making the problem code match the working code by eliminating the With statements but that did not resolve the problem.

    Is it likely or even possible that my Excel file has become corrupted in some way? I truly appreciate everyone who takes the time to look at this. You're the real heroes.

    If I uncomment any commented line in the "'Select for batch" section at the end of this Private Sub I get the runtime error:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    Dim lTargetRow As Long
    Dim lLastRow As Long
    Dim lLastColumn As Long
    Dim sQueryID As String
    
    Dim rTarget As Range
    
    
    With Me
        lTargetRow = Target.Row
        lLastRow = .Cells(65000, 1).End(xlUp).Row
        lLastColumn = .Cells(20, 255).End(xlToLeft).Column
    
        'run query
        If Target.Row <= lLastRow And Target.Column = 3 Then
            sQueryID = .Cells(Target.Row, 1).Value
            RunQuery (sQueryID)
        End If
    
        'retrieve query
        If Target.Row <= lLastRow And Target.Column = 4 Then
            sQueryID = .Cells(Target.Row, 1).Value
            Call RetrieveQuery(sQueryID)
        End If
        
        'Delete Query
        If Target.Row <= lLastRow And Target.Column = 5 Then
            sQueryID = .Cells(Target.Row, 1).Value
            Call DeleteQuery(sQueryID, lTargetRow)
        End If
        
        'Select for batch
        If Target.Row <= lLastRow And Target.Column = 6 Then
            If Target.Value = "Select" Then
                With Target
                    .Value = "Run"
    '                .Interior.Pattern = xlSolid
    '                .Interior.PatternColorIndex = xlAutomatic
    '                .Interior.ThemeColor = xlThemeColorAccent6
    '                .Interior.TintAndShade = 0.599993896298105
    '                .Interior.PatternTintAndShade = 0
    '                .HorizontalAlignment = xlCenter
                End With
            Else
                With Target
                    .Value = "Select"
    '                .Interior.Pattern = xlPatternSolid
    '                .Interior.PatternColorIndex = xlAutomatic
    '                .Interior.ThemeColor = xlThemeColorDark1
    '                .Interior.TintAndShade = -4.99893185216834E-02
    '                .Interior.PatternTintAndShade = 0
    '                .HorizontalAlignment = xlCenter
                End With
            End If
        End If
        
    
    End With
        
    Cancel = True
    
    End Sub
    


    This Private Sub in a different Worksheet runs fine:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    Dim lLastRow As Long
    Dim lLastColumn As Long
    Dim rngData As Range
    
    With Me
        
        lLastRow = .Cells(65000, 1).End(xlUp).Row
        lLastColumn = .Cells(20, 255).End(xlToLeft).Column
    
        If Target.Row = 20 And Target.Column < lLastColumn + 1 Then
            .Unprotect
            Set rngData = .Range(.Cells(20, 1), .Cells(lLastRow, lLastColumn))
            If Target.Interior.PatternColorIndex <> 3 Then
                rngData.Sort key1:=.Cells(1, Target.Column), order1:=xlAscending, Header:=xlYes
                Target.Interior.PatternColorIndex = 3
            Else
                rngData.Sort key1:=.Cells(20, Target.Column), order1:=xlDescending, Header:=xlYes
                Target.Interior.PatternColorIndex = 4
            End If
        End If
        
        If bDefinitionsProtected = True Then
            .Protect AllowSorting:=True, AllowFiltering:=True
            .EnableSelection = xlUnlockedCells
        End If
        
    End With
        
    Cancel = True
    
    End Sub

    Note: I'm not very concerned about what color the cells are filled with but when .Value is set to "Select" the fill should be very light grey and when the .Value is set to "Run" the fill should be a light green. The property values I am using came from a macro I recorded then deleted just to get those values.

    Note2: In the code that works, the second block, the fill colors are pretty much indistinguishable. I'm just using the  value in Target.Interior.PatternColorIndex to toggle between ascending and descending sorts.

    Thanks to anyone who read all the way down to this point.

    Thanks VERY much to anyone who can shed some light on this issue.


    • Edited by dkingston Thursday, August 20, 2020 3:35 PM Solved the problem
    • Marked as answer by dkingston Thursday, August 20, 2020 3:35 PM
    Thursday, August 20, 2020 3:25 PM