Unhide Rows Macro issue

[achilles18]

Hi All,

I am currently using a macro script within my workbook to un-hide hidden rows which I got from here thank you to Rick Rothstein!! It works really well until I try to protect my work sheet and then execute the script.

Here is a copy of the exact code I am using

Private Sub CommandButton1_Click()
'Add Scope
  If Rows(42).Hidden Then
    Rows(42).Hidden = False
  Else
    On Error Resume Next
    With Range("A41:A59").SpecialCells(xlVisible)
      .Areas(1)(.Areas(1).Count).Offset(1).EntireRow.Hidden = False
    End With
    On Error GoTo 0
  End If
End Sub

So to break it down and try to provide enough information. At the start I went through and formatted the cells within the worksheet and locked/un-locked what ever I needed to. I then hid the rows which I didn't want seen. I then went and Protected the worksheet, only allowing "Select Locked Cells and Select Unlocked cells" to be accessed. When doing this and running the macro (via a active x command button) I got the following error "Run-time error '1004': Unable to set the Hidden property of the Range class".

So I unlocked the work sheet and then relocked it allowing "Format Columns, Format rows, Insert Columns and Insert Rows" this time the macro worked, but instead of cycling through and un-hiding row after row it only un-hides one row and that is it.

Any idea why it would only un-hide one row and then stop? It works fine as soon as I un-protect the worksheet again.

Thanks in advance, I hope that was enough info.

Regards

 

[achilles18]

Ok so just had a thought. Is there a way that I could build into my macro to un-protect the work sheet, un-hide the row and then re protect the sheet?

Just a thought..

Any ideas would be greatly appreciated

 

[JoeMo]

Hi All,

I am currently using a macro script within my workbook to un-hide hidden rows which I got from here thank you to Rick Rothstein!! It works really well until I try to protect my work sheet and then execute the script.

Here is a copy of the exact code I am using

Private Sub CommandButton1_Click()
'Add Scope
  If Rows(42).Hidden Then
    Rows(42).Hidden = False
  Else
    On Error Resume Next
    With Range("A41:A59").SpecialCells(xlVisible)
      .Areas(1)(.Areas(1).Count).Offset(1).EntireRow.Hidden = False
    End With
    On Error GoTo 0
  End If
End Sub

So to break it down and try to provide enough information. At the start I went through and formatted the cells within the worksheet and locked/un-locked what ever I needed to. I then hid the rows which I didn't want seen. I then went and Protected the worksheet, only allowing "Select Locked Cells and Select Unlocked cells" to be accessed. When doing this and running the macro (via a active x command button) I got the following error "Run-time error '1004': Unable to set the Hidden property of the Range class".

So I unlocked the work sheet and then relocked it allowing "Format Columns, Format rows, Insert Columns and Insert Rows" this time the macro worked, but instead of cycling through and un-hiding row after row it only un-hides one row and that is it.

Any idea why it would only un-hide one row and then stop? It works fine as soon as I un-protect the worksheet again.

Thanks in advance, I hope that was enough info.

Regards

This part of the code you posted only unhides a single row:

With Range("A41:A59").SpecialCells(xlVisible)       
.Areas(1)(.Areas(1).Count).Offset(1).EntireRow.Hidden = False
    End With

There is nothing in your code to deal with more than one row.

 

[Fishboy]

I unlocked the work sheet and then relocked it allowing "Format Columns, Format rows, Insert Columns and Insert Rows" this time the macro worked

This is correct. I have a number of protected workbooks with hide row / column macros. So long as the Format Rows and Format Columns options are ticked the hide macros should still work.

Ok so just had a thought. Is there a way that I could build into my macro to un-protect the work sheet, un-hide the row and then re protect the sheet?

This is possible yes. Start your macro with this (where password is amended to whatever password you have used to protect the sheet):

Sub SUBNAME()
ActiveSheet.Unprotect "password"
Put whatever the rest of your code is supposed to do here in the middle, then end the macro with this
ActiveSheet.Protect Password:="password", AllowFormattingColumns:=True, AllowFormattingRows:=True
End Sub

 

[achilles18]

This is correct. I have a number of protected workbooks with hide row / column macros. So long as the Format Rows and Format Columns options are ticked the hide macros should still work.


This is possible yes. Start your macro with this (where password is amended to whatever password you have used to protect the sheet):

Sub SUBNAME()
ActiveSheet.Unprotect "password"
Put whatever the rest of your code is supposed to do here in the middle, then end the macro with this
ActiveSheet.Protect Password:="password", AllowFormattingColumns:=True, AllowFormattingRows:=True
End Sub

Hi Fishboy, Thank you so much for the reply. The unlock and lock works perfectly!!

Thanks again for the reply and my apologies for taking so long to get back to you!

Regards