Posts by nitrox_john

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    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'.

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

    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.