option button to hide rows, with password

[Guest]

Dear Experts,

I would like to provide an option button to enable users to hide preselected
rows (25 to 128) in a worksheet. Click on the button, the rows are hidden,
unclick the button the rows reappear. I have tried the following code in the
Sheet 5 (Code) window of the VB screen:

Private Sub OptionButton1_Click()
If OptionButton1.Value = True Then
Row("25:128").Select
Selection.EntireRow.Hidden = True
Else
Row("25:128").Select
Selection.EntireRow.Hidden = False
End If
End Sub

This code works to hide the rows, but the rows do not reappear (possibly
because the option button doesn't respond to the second click to clear the
button). I would appreciate any help please.

As an unexpected bonus, it would be excellent if the main user could be
asked for a user-specified password after clicking the option button to hide
the rows so the hidden rows are protected from viewing by others who access
the file. Then, when the main user wants to view the hidden rows, clicking
on the option button to clear the button would prompt the main user for his
password, after which the rows would unhide. If code can't do this, then I
will direct the user to click on Tools, Protection, Protect Sheet to password
protect the sheet after hiding the rows.

Thank you for your expertise and assistance.

 

[CoRrRan]

This should work:
*******************************************
Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Rows("25:128").EntireRow.Hidden = True
Else
Rows("25:128").EntireRow.Hidden = False
End If
End Sub
********************************************

But use a CheckBox instead of an OptionButton, as a CheckBox can be made
True and False, while an OptionButton requires 2 OptionButtons to change
state.

HTH,
CoRrRan

 

[Guest]

Hi,

Thank you for the quick reply and idea for a check box not option button. I
copied your code into the VB screen and get the following error:

Run time error 1004
Unable to set the Hidden property of the Range class.

I'm not sure what this means. How can I fix this bug? Thanks for your help!

 

[CoRrRan]

You have to have an unprotected worksheet when you are hiding or showing
rows or columns.

HTH,
CoRrRan

 

[Guest]

Hi CoRrRan,

It works - you genius! I first entered your suggested code into the file on
Excel 1997 and it didn't work. The exact same code in Excel 2000 works fine!
Neither worksheet was protected. If you have any ideas why it didn't work
in Excel 97, it would be appreciated because some of my users still have
Excel 97.

I have tried to add the password protection prompt into the code as well so
the user is prompted for a password when hiding and unhiding the rows via the
check box. So far no luck. Any ideas would be extremely welcome.

Cheers -
RRP