option button to hide rows, with password

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.

=?Utf-8?B?UlJQMzMz?= <[email protected]> wrote in
news:[email protected]:

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

> 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.
>

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" wrote:

> =?Utf-8?B?UlJQMzMz?= <[email protected]> wrote in
> news:[email protected]:
>
> 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
>
> > 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.
> >
>
>

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

HTH,
CoRrRan

=?Utf-8?B?UlJQMzMz?= <[email protected]> wrote in
news:[email protected]:

> 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" wrote:
>
>> =?Utf-8?B?UlJQMzMz?= <[email protected]> wrote in
>> news:[email protected]:
>>
>> 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
>>
>> > 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.
>> >
>>
>>
>

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

"CoRrRan" wrote:

> You have to have an unprotected worksheet when you are hiding or showing
> rows or columns.
>
> HTH,
> CoRrRan
>
> =?Utf-8?B?UlJQMzMz?= <[email protected]> wrote in
> news:[email protected]:
>
> > 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" wrote:
> >
> >> =?Utf-8?B?UlJQMzMz?= <[email protected]> wrote in
> >> news:[email protected]:
> >>
> >> 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
> >>
> >> > 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.
> >> >
> >>
> >>
> >
>
>