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

jpbonono

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

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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

Hi Fishboy,

Please guide me on how to apply that on my code. Please also bear with me as I am a beginner. Thank you for your patienc
 
Upvote 0
No worries :)

i'm just doing some testing of my own and will get back to you shortly once I have made sure it is working
 
Upvote 0
2 quick questions...

is Boys a named range?

Is this a macro that runs when you click a button?
 
Upvote 0
I think the problem was with when I copy / pasted my code last time. The sub name and first line of code were all on the same line.

Try this:

Code:
Private Sub Boys_Click()
Application.ScreenUpdating = False
If Boys.Value = True Then 
ActiveSheet.Unprotect ("[B]password"[/B])
Rows("13:57").EntireRow.Hidden = True
ActiveSheet.Protect ("[B]password[/B]") 
Else
ActiveSheet.Unprotect ("[B]password[/B]") 
Rows("13:57").EntireRow.Hidden = False   
End If 
ActiveSheet.Protect ("[B]password[/B]")
End Sub
 
Upvote 0
I have a sneaky suspicion we are over complicating this. If the macro purely needs to hide or unhide the specified rows on button press, with no conditionals, just on button press try this:

Code:
Sub Boys_Click()
Application.ScreenUpdating = False
ActiveSheet.Unprotect ("password")
Rows("13:57").Hidden = Not Rows("13:57").Hidden
ActiveSheet.Protect ("password")
End Sub
 
Upvote 0
I think the problem was with when I copy / pasted my code last time. The sub name and first line of code were all on the same line.

Try this:

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

Thank you so much! It's now working, now my next question is, is this going to work if I make a vba for copy sheet?
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

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
Back
Top