Runtime error 1004: Unable to set the hidden property of range class

[Loobrush]

Hi all. Having a bit of a problem getting one of my macros to work in excel 2007. I've created a drop down list in 'Front Page' with the number of rows I want to be displayed in a table in another sheet 'Detail No.', but I keep getting [Runtime error 1004: Unable to set the hidden property of range class]. I tried unprotecting my worksheet, but that didn't seem to help (Not that I really want to leave it unprotected anyway). Help would be much appreciated, here is the code at present along with the document:

TEST.xls

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rowNum As Long, offsetVal As Long
If Not Application.Intersect(Target, Range("G6")) Is Nothing Then
offsetVal = Application.Max(0, Val(Range("G6").Text))
For rowNum = 2 To 21
Sheets(2).Rows(rowNum).Hidden = (1 < rowNum - offsetVal)
Next rowNum
End If
End Sub

Thanks

Loobrush

[Andy Pope]

You need to unprotect the sheet before hiding rows and then reapply protection.

Currently with sheet unprotected your code works

[Loobrush]

Yea that might be a bit of a problem as I need it protected, is there anyway I can get it to unprotect itself when the drop down selection is made then reprotect itself?

[Andy Pope]

Please Login or Register  to view this content.

[Loobrush]

That's excellent, thank you!

Just one more tidbit, it's not vital, but would be helpful. If my sheets are password protected is there any way I can make that code still work without the need to enter the password?

[Andy Pope]

check the help for what parameters can be set when un/protecting. One of them is a password.

[Loobrush]

Sorted. Thank you very much for your help.