Unhide Rows Macro issue

achilles18

New Member
Joined
May 24, 2015
Messages
20
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
Code:
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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

achilles18

New Member
Joined
May 24, 2015
Messages
20
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,392
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Code:
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:
Code:
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

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
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

New Member
Joined
May 24, 2015
Messages
20
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,535
Messages
5,602,217
Members
414,513
Latest member
junbuggle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top