I'm using the following simple codes to hide or unhide rows, upon clicking an option button:
[vba]
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
[/vba]
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?
Unprpotect the sheet, hide them, then protect again.
Is that going to work, if the user is expected to be cycling back and forth from auto data entry to manual data entry?
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
This works for me
[vba]
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
[/vba]
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)
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.
[VBA]
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
[/VBA]
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
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
[vba]
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
[/vba]
That is much cleaner. Thanks Bob. I am just trying to provide solutions with my limited skills. Learning every day.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Am I missing a step/definition in this? Simply writing:
[VBA]
Me.Unprotect Password:="password"
[/VBA]
returns an error.
Why would it?Originally Posted by K. Georgiadis
My fault; I was actually referring to an error being returned from your previous suggestion:
[VBA]Sub Manual_Click()[/VBA]
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?
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.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
I'm a (perpetual) neophyte, so I'll ask another dumb question: if the entire workbook is protected with a specific password, how does
[VBA]
Me.Unprotect
[/VBA]
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!
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.....
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
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!)
Let us know how this works out for you.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
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"!!)
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
Reply With Quote
