PDA

View Full Version : Solved: Can rows be hidden/unhidden in spite of sheet protection?



K. Georgiadis
03-31-2006, 09:00 AM
I'm using the following simple codes to hide or unhide rows, upon clicking an option button:


Sub Manual_Click()
Range("35:35").EntireRow.Hidden = True
Range("36:36").EntireRow.Hidden = False
End Sub

Sub Auto_Click()
Range("35:35").EntireRow.Hidden = False
Range("36:36").EntireRow.Hidden = True
End Sub


Not surprisingly, I get a Runtime 1004 error when the workbook is password-protected. Can this be overcome by entering an exception in the ProtectAll routine?

Bob Phillips
03-31-2006, 09:03 AM
Unprpotect the sheet, hide them, then protect again.

K. Georgiadis
03-31-2006, 09:11 AM
Is that going to work, if the user is expected to be cycling back and forth from auto data entry to manual data entry?

K. Georgiadis
03-31-2006, 09:52 AM
In fact I did try your suggestion but, upon protecting the workbook, I still get a runtime 1004 error. The problem may be that the pair of option buttons act like toggle switches that keep switching the hidden/unhidden property of rows 35 and 36 -- one hidden, the other unhidden

Bob Phillips
03-31-2006, 10:37 AM
This works for me


Sub Manual_Click()
Me.Unprotect
Rows(35).Hidden = True
Rows(36).Hidden = False
Me.Protect
End Sub

Sub Auto_Click()
Me.Unprotect
Rows(35).Hidden = False
Rows(36).Hidden = True
Me.Protect
End Sub

K. Georgiadis
03-31-2006, 10:45 AM
Thank you. I had come up the idea of making an unprotect/protect routin part of the click macros, but your code is more streamlined and I'm going to give it a try.

(I would mark this as "SOLVED" except that I no longer know how)

lucas
03-31-2006, 10:50 AM
You can use an actual toggle button from the controls toolbar and add this to the sheet code. be sure to check the name of the sheet and password.


Private Sub ToggleButton1_Click()
'replace password with your actual password if one is used
'use "" for no password
Worksheets("Sheet1").Unprotect Password:="password"

If ToggleButton1.Value = True Then
'This area contains the things you want to happen
'when the toggle button is not depressed
Rows(35).EntireRow.Hidden = True
Rows(36).EntireRow.Hidden = False
Else
'This area contains the things you want to happen
'when the toggle button is depressed
Rows(35).EntireRow.Hidden = False
Rows(36).EntireRow.Hidden = True

Worksheets("Sheet1").Protect Password:="password"
End If
End Sub

Bob Phillips
03-31-2006, 10:56 AM
Steve,

You don't need Rows and Entirerow, they are the same thing. Using a TG as you do also allows a huge simplification of the code


Private Sub ToggleButton1_Click()
'replace password with your actual password if one is used
'use "" for no password
Me.Unprotect Password:="password"
Rows(35).Hidden = ToggleButton1.Value
Rows(36).Hidden = Not ToggleButton1.Value
Me.Protect Password:="password"
End Sub

lucas
03-31-2006, 11:04 AM
That is much cleaner. Thanks Bob. I am just trying to provide solutions with my limited skills. Learning every day.

K. Georgiadis
03-31-2006, 12:59 PM
Am I missing a step/definition in this? Simply writing:


Me.Unprotect Password:="password"



returns an error.

Bob Phillips
03-31-2006, 01:01 PM
Am I missing a step/definition in this? Simply writing:


Me.Unprotect Password:="password"


returns an error.

Why would it?

K. Georgiadis
03-31-2006, 01:15 PM
My fault; I was actually referring to an error being returned from your previous suggestion:



Sub Manual_Click()
Me.Unprotect
Rows(35).Hidden = True
Rows(36).Hidden = False
Me.Protect
End Sub

Sub Auto_Click()
Me.Unprotect
Rows(35).Hidden = False
Rows(36).Hidden = True
Me.Protect
End Sub


Does it need a definition of password?

lucas
03-31-2006, 01:30 PM
No you don't need to dim password.
I've learned its good practice to use Option Explicit at the top of each module, sheet code, etc. then you will know if you have a variable to dim that you missed.

K. Georgiadis
03-31-2006, 01:37 PM
No you don't need to dim password.
I've learned its good practice to use Option Explicit at the top of each module, sheet code, etc. then you will know if you have a variable to dim that you missed.
I'm a (perpetual) neophyte, so I'll ask another dumb question: if the entire workbook is protected with a specific password, how does


Me.Unprotect


unlock the sheet so that the rows can be hidden/unhidden?

My greater objective is to re-protect the sheet immediately after the option button has been allowed to hide/unhide rows, because the worksheet has a number of cells that must stay protected. For now, I have solved my problem by attaching to each click macro a routine that unprotects the entire workbook with the password and immediately re-protects it after the option click has done its thing. Programmatically, inefficient but I was in a pinch!

lucas
03-31-2006, 01:48 PM
I may not understand you correctly but the toggle should show/hide the rows even if the workbook is password protected I think. You just can't change anything in the cells.....

K. Georgiadis
03-31-2006, 01:59 PM
I'll have to try the toggle approach. My initial design was based on two option buttons, so I stayed with it -- at least for now. Thanks for your help (and your patience!)

lucas
03-31-2006, 02:07 PM
Let us know how this works out for you.

K. Georgiadis
03-31-2006, 03:46 PM
Let us know how this works out for you.
worked perfectly! As soon as I get the chance, I will switch from option buttons to a toggle switch. Thanks!!!

(If I could only mark this "SOLVED"!!)

mdmackillop
03-31-2006, 03:52 PM
(If I could only mark this "SOLVED"!!)
:thumb

K. Georgiadis
03-31-2006, 03:55 PM
:beerchug:

Bob Phillips
03-31-2006, 03:57 PM
worked perfectly! As soon as I get the chance, I will switch from option buttons to a toggle switch. Thanks!!!

(If I could only mark this "SOLVED"!!)

A toggle switch is no better than option buttons, it is just a matter of preference, and is not the reason it works. If the sheet is not protetcted, there is no need to unprotect it when hiding/unhiding rows.