Posts by rory

    Quick question - are you on version 2212 by any chance? I've seen quite a few reports recently of weird errors with workbooks with userforms on that build. There is apparently a fix in the works.

    If you only need this range to be processed in a macro, why not just pass the dynamic range you already have and simply process every other row in the code (Pretty much as Carim showed near the beginning of this thread). Or do you need this named range for something else?

    When you show a form, it is first loaded into memory and any code in its Initialize event is run (this is usually where the problem with a line actually is) then the form is displayed and any Activate code is run.

    As a side note, it is generally better in my experience not to run code directly from the Workbook_Open event. If you use Application.Ontime to schedule any startup code (you can use Now as the schedule time), it gives Excel time to finish all its startup processes (particularly necessary when you open the workbook directly from explorer, thereby also loading excel).

    Try this:

    As I said, either do it in sections:


    or use Union:


    For the other part you can use conditional formatting to colour cells that are not empty.

    This is not very good code and probably shouldn't be used. It definitely shouldn't be used as a teaching tool other than for showing some things not to do.

    I would suggest you put a formula somewhere (eg on the Admin sheet) that checks if all the relevant cells have been filled in and returns TRUE/FALSE accordingly. Then in the ThisWorkbook module of the workbook, add:

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Cancel = (Sheets("Admin").Range("Z1").Value = False)
    End Sub

    (I've assumed you use Z1 on the admin sheet for the formula)

    Doing it this way means you only need to adjust the formula if the worksheet layout changes, not the code.

    Clearly (or hopefully) that isn't the actual code since the range address is invalid. Based on what it looks like you're doing, and given that you didn't say what the error was, I'd guess that the address you are passing is longer than 255 characters, which Range won't accept. Either do it in chunks or use Union to create one range from separate ones.