Macro no longer works in Excel 2007

[The Cymro Card]

I have a workbook / macro that no longer works since I moved to Excel 2007.

When running some code that hides / unhides rows it gives me

Runtime Error 1004 - unable to set the Hidden property of the range class.

The offending line of code is

Rows("35:40").Hidden = True

Now, I thought this may be due to the worksheet being protected; however I do have the code

ActiveSheet.Unprotect Password:=Range("Developer_Password").Value

before the offending line, and I can manually hide the rows in question if I go back to the Excel workbook. So it feels as though it's VBA not recognising the sheet is now unprotected

Additional potential complications

Although this happens when I run a macro, the line itself is in a change event within a worksheet.

The spreadsheet itself is created from a template file. The macro is called from an xla add-in.



As a slight aside, is there any one-stop location for seeing what doesn't translate well into 2007... other macros don't work either (but do on older versions of excel)

 

[Jerry Sullivan]

Hello and Welcome,

Although this happens when I run a macro, the line itself is in a change event within a worksheet.

It's hard to say without seeing all the code, but my guess is that the problem is related to having your macro trigger a worksheet event.

You'll have more predictable results if your have your macro temporarily disable events and then re-enable them before ending. If you want the Rows to be Hidden, you can add that line of code to your macro.

Your revised macro code might look like...

Sub MyMacro()
    Application.EnableEvents = False
    '.... first part of your current code here
    Rows("35:40").Hidden = True
 
    '.... rest of your current code continues here
    Application.EnableEvents = True
End Sub

Sorry that I can't shed any light on why your macro worked with xl2003 but not xl2007.

Hope this helps.