Posts by nitrox_john

Good tip! Thanks.

Now resolved...

Thanks for your help too, Rory. Unfortunately, it was not easy to send you the worksheet, as it is one of several in an interrelated system, and much of the data is confidential in nature.

Apologies if I appeared to ignore your advice.

Cheers, John.

BINGO! Rory, you've spotted it. Thank you.

Yes, the sheet was protected. The cell in question ('NeutralCell') was unprotected, but apparently this only allows setting cell value, not other properties.

Not sure where I missed this along the way, but glad to have it resolved.

Props to you!

Cheers, John.

Interestingly, it would appear that only the .Value assignment works. I have tried various others apart from .Interior.Color, such as .Font.Color, .Width, even .Text, and they all fail, although the error codes differ slightly.

Many thanks again, Roy.

I have tried this before but will try it again now.

Yep, same result. I have tried specifying the worksheet both directly and using with - end with. Result is always the same.

I have also used both Color and ColorIndex, but the result is also the same.

Is there a document anywhere about the sequence in which Excel sets up the VBA environment on invocation? Maybe it's a sequencing issue?

But then again, how come the .Value assignment on the same cell works reliably, and the .Color assignment reliably fails? And debug shows that all public declarations are set...

Cheers, John.

Hi Roy,

Thanks for coming back so quickly. InDeveloperMode is a standard module of mine that simply determines if an XLTM is opened with 'New' or 'Open'.

Function InDeveloperMode() As Boolean

' Determines whether a macro template was opened with a 'New' or an 'Open' command

    Dim fso As Object
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    If fso.GetExtensionName(ThisWorkbook.Name) = "xltm" Then
        InDeveloperMode = True
    Else
        InDeveloperMode = False
    End If

End Function

This is working fine.

The problem is quite specific to Range(...).Interior.Color = ... in this Open_Workbook context.

If you even have some troubleshooting tips (beyond the usual, which I have tried), I would be happy to hear of them.

Cheers, John.

Hi all, first post, but long-time appreciator of OzGrid. Got a quirky one.

Range(...).Interior.Color = vbRed (or any other color) during Workbook_Open sub results in Run-time error 1004, Application- or object-defined error.

Preceding lines that reference the same cell do not, and work as expected. Code follows...

Private Sub Workbook_Open()

' Constant initialisation...

    Set Main_worksheet = ThisWorkbook.Worksheets(Transactions_wsname)

' Code start...

    If InDeveloperMode Then
        Range(NeutralCell).Value = "Developer Mode"
        Range(NeutralCell).Interior.Color = vbRed
    Else
        Range(NeutralCell).Value = ""
        Range(NeutralCell).Interior.Color = xlNone
        Call Main_Workbook_Open
    End If
    
End Sub

In the above, the cell value is correctly set, but the sub fails on setting the color.

NeutralCell is defined in a Public_Declarations module as follows:-

Public Const NeutralCell As String = "L1"

Debug shows NeutralCell = "L1", range is correctly set, and vbRed is 255.

The same error occurs on the corresponding lines after Else (i.e. not InDeveloperMode). The value is cleared, and the color set gives error 1004.

Any ideas?

Thanks, John.