[vba] cannot hide/unhide rows when sheet is protected

[jpbonono]

Hi,

I have this code in my module

Private Sub Boys_Click()If Boys.Value = True Then
Rows("13:57").EntireRow.Hidden = True
Else
 Rows("13:57").EntireRow.Hidden = False
  End If
End Sub

However, my problem is whenever i protect the sheet it doesnt allow me to proceed(debug mode) since i am fully aware that some cells on the rows are protected to.

(I already tried modified the default cell locking(allowing rows) and still wont work)

Are there any work arounds here that would let excel allow the hiding/unhiding feature?

Thank you in advance.

 

[Fishboy]

Hi jpbonono,

im fairly sure if format rows and format columns are ticked when you choose the cell protection rights it should work regardless. If for some reason it still won't work you can add a couple of lines into your macro that will unprotected the sheet, hide / unhide rows, then re-protect the sheet.

i'm currently posting from my iPad but when I get back to my pc I will try and give you some example code to work with

 

[Fishboy]

Private Sub Boys_Click()If Boys.Value = True Then 
ActiveSheet.Unprotect ("[B]password[/B]")
Rows("13:57").EntireRow.Hidden = True 
Else  
Rows("13:57").EntireRow.Hidden = False   
End If 
ActiveSheet.Protect ("[B]password[/B]")
End Sub

Where the bold password can be amended to whatever your password is.

 

[jpbonono]

Thank you for a quick reply fishboy, I've already did your suggestion. Your second option however, when I do the unprotecting codes it will also reveal the formulas on some cells that are part of the rows and that will be a big problem.

 

[jpbonono]

okay im gonna try that first.

 

[jpbonono]

The codes wont work.

 

[Fishboy]

Thank you for a quick reply fishboy, I've already did your suggestion. Your second option however, when I do the unprotecting codes it will also reveal the formulas on some cells that are part of the rows and that will be a big problem.

The process of unprotecting the sheet, hiding the rows and reprotecting the sheet is pretty much instantaneous. You shouldn't really see your formulas on screen. You can always add this to the start of the macro to prevent screen flicker etc
Application.ScreenUpdating = False

 

[Fishboy]

The codes wont work.

Can you elaborate a bit? Does it error? Does it do nothing? Does it start and then fail part way through?

 

[jpbonono]

...

 

[jpbonono]

Can you elaborate a bit? Does it error? Does it do nothing? Does it start and then fail part way through?

It opens the module and requesting for a debug.