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

jpbonono

Board Regular
Joined
Aug 18, 2013
Messages
148
Hi,

I have this code in my module

Code:
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

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

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

Board Regular
Joined
Aug 18, 2013
Messages
148
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

Board Regular
Joined
Aug 18, 2013
Messages
148
okay im gonna try that first.
 

Fishboy

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

Forum statistics

Threads
1,081,678
Messages
5,360,472
Members
400,588
Latest member
SpannersWatson

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top