hide rows with macro/togglebutton

[huntermcg]

hello,

I want to be able to hide and unhide rows by clicking a togglebutton.

So far, I managed to record 2 macro's. 1 to hide the rows and 1 to unhide the rows. I could make to button's to assign each macro to, but that would be a lot of work, since I want to add this function to several places in my worksheet.

A togglebutton would do the trick, but I do not know how to assign the 2 macro's to it. Is there any pro out there who could help me out ?

At least I think the toggle button would be in my humble opinion the best solution. The only thing is that I have to put the togglebutton somewhere outside the rows I wish to hide/unhide. If there is a better solution thinkable I am happy to hear ?

Next to this, I protect my sheets. With the 2 button's I managed to fix myself I get a 'false' error when I protect my sheet. I am not sure what I am doing wrong?

[Bob Phillips]

You don't need a toggle button, just a command button. Just use code like

Rows("10:12").Hidden = Not Rows("10:12").Hidden

--
HTH

Bob Phillips

"huntermcg" <[email protected]> wrote
in message news:[email protected]...
>
> hello,
>
> I want to be able to hide and unhide rows by clicking a togglebutton.
>
> So far, I managed to record 2 macro's. 1 to hide the rows and 1 to
> unhide the rows. I could make to button's to assign each macro to, but
> that would be a lot of work, since I want to add this function to
> several places in my worksheet.
>
> A togglebutton would do the trick, but I do not know how to assign the
> 2 macro's to it. Is there any pro out there who could help me out ?
>
> At least I think the toggle button would be in my humble opinion the
> best solution. The only thing is that I have to put the togglebutton
> somewhere outside the rows I wish to hide/unhide. If there is a better
> solution thinkable I am happy to hear ?
>
> Next to this, I protect my sheets. With the 2 button's I managed to fix
> myself I get a 'false' error when I protect my sheet. I am not sure what
> I am doing wrong?
>
>
> --
> huntermcg
> ------------------------------------------------------------------------
> huntermcg's Profile:
http://www.excelforum.com/member.php...o&userid=19391
> View this thread: http://www.excelforum.com/showthread...hreadid=469683
>

[huntermcg]

great. this works!

now I still only have one problem. when I want to protect my sheet I get an error that he cannot execute the macro. Do you know what this is, and how to solve it?

[huntermcg]

... addition to former reply post:

it is a Run-time error '1004':

this is my code.

Sub Macro1()
Rows("4:20").Hidden = Not Rows("4:20").Hidden
End Sub
Sub Macro2()
Rows("22:39").Hidden = Not Rows("22:39").Hidden
End Sub
Sub Macro3()
Rows("40:54").Hidden = Not Rows("40:54").Hidden
End Sub
Sub Macro4()
Rows("55:68").Hidden = Not Rows("55:68").Hidden
End Sub

maybe this helps ...

[Bob Phillips]

unprotect the sheet just prior to the code, then protect it after.

--
HTH

Bob Phillips

"huntermcg" <[email protected]> wrote
in message news:[email protected]...
>
> .. addition to former reply post:
>
> it is a Run-time error '1004':
>
> this is my code.
>
> Sub Macro1()
> Rows("4:20").Hidden = Not Rows("4:20").Hidden
> End Sub
> Sub Macro2()
> Rows("22:39").Hidden = Not Rows("22:39").Hidden
> End Sub
> Sub Macro3()
> Rows("40:54").Hidden = Not Rows("40:54").Hidden
> End Sub
> Sub Macro4()
> Rows("55:68").Hidden = Not Rows("55:68").Hidden
> End Sub
>
> maybe this helps ...
>
>
> --
> huntermcg
> ------------------------------------------------------------------------
> huntermcg's Profile:
http://www.excelforum.com/member.php...o&userid=19391
> View this thread: http://www.excelforum.com/showthread...hreadid=469683
>

[huntermcg]

I do not understand. I did that.

I had to unprotect the sheet to make the code/macro work.

[Bob Phillips]

What Excel version are you running?
What level of protection?
How is the macro invoked?

--
HTH

Bob Phillips

"huntermcg" <[email protected]> wrote
in message news:[email protected]...
>
> I do not understand. I did that.
>
> I had to unprotect the sheet to make the code/macro work.
>
>
> --
> huntermcg
> ------------------------------------------------------------------------
> huntermcg's Profile:
http://www.excelforum.com/member.php...o&userid=19391
> View this thread: http://www.excelforum.com/showthread...hreadid=469683
>

[huntermcg]

What Excel version are you running?

Xp 2003

What level of protection?

secure sheet and workbook, standard in excel.

How is the macro invoked?

how do you mean invoked ? i made the macro as you said, like you can read in a former post. then i put a commandbutton on the sheet and assigned the macro to the button. thats it. then i only want to secure the sheet again and he gives the error.

i can only go around it if i allow users to style the rows. but that is not what i want.

[huntermcg]

Hello Bob or any PRO reading this,

If you have a suggestion please reply to my last post.

Best regards, Hunter

[STEVE BELL]

Bob has pretty much laid out what you need.
Sounds like you are having difficulty with what he has written.

So let's start anew...

Post your code again and give us a concise description of what is happening
or not happening.

It does sound like all you need to do is wrap your code with unprotect and
protect

Sub MySub()
Activesheet.Unprotect

'Do my stuff

Activesheet.Protect
End Sub

--
steveB

Remove "AYN" from email to respond
"huntermcg" <[email protected]> wrote in
message news:[email protected]...
>
> Hello Bob or any PRO reading this,
>
> If you have a suggestion please reply to my last post.
>
> Best regards, Hunter
>
>
> --
> huntermcg
> ------------------------------------------------------------------------
> huntermcg's Profile:
> http://www.excelforum.com/member.php...o&userid=19391
> View this thread: http://www.excelforum.com/showthread...hreadid=469683
>

[huntermcg]

I just have a page at wich I have set a few macro command buttons. The button will hide or unhide a couple of rows.

I cannot protect the sheet without allowing the row style to be edited while securing the sheet.

Where do I put the code like you suggested ? The worksheet itself does not have any VBA coding. The macro's are put in a seperate module. Do they need to be at the VBA page of the worksheet itself? Macro coding is like this:

Sub Macro1()
Rows("4:20").Hidden = Not Rows("4:20").Hidden
End Sub
Sub Macro2()
Rows("22:39").Hidden = Not Rows("22:39").Hidden
End Sub
Sub Macro3()
Rows("40:54").Hidden = Not Rows("40:54").Hidden
End Sub
Sub Macro4()
Rows("55:68").Hidden = Not Rows("55:68").Hidden
End Sub

The macro's noe get errors when i protect the sheet without allowing the rows to be edited. But that is not what I want. Hope you understand my case better and can help me to turn it around .. thanks for your input so far.

Best regards,
Hunter

[STEVE BELL]

The buttons have their own code (Toolbar button) or are attached to a macro
(Forms Toolbar button)

If it is a toolbar button your code goes into the worksheet module
Private Sub CommandButton1_Click()
Macro1
End Sub

with Macro1 in a standard module

or you can move your code into the click event.



If it is a Forms toolbar button you just assign Macro1 to it.

To get around the protection issue - just amend your code slightly

Sub Macro1()
activesheet.unprotect
Rows("4:20").Hidden = Not Rows("4:20").Hidden
activesheet.protect
End Sub

Do the same for each of the macros.

Let me know if this helps.

And I am not sure just how you want to handle sheet protection, when you
want it protected, and whether or not you want cells available to the user
for input.

Post back and we'll get this thing working just like you want...

keep on Exceling...
--
steveB

Remove "AYN" from email to respond
"huntermcg" <[email protected]> wrote
in message news:[email protected]...
>
> I just have a page at wich I have set a few macro command buttons. The
> button will hide or unhide a couple of rows.
>
> I cannot protect the sheet without allowing the row style to be edited
> while securing the sheet.
>
> Where do I put the code like you suggested ? The worksheet itself does
> not have any VBA coding. The macro's are put in a seperate module. Do
> they need to be at the VBA page of the worksheet itself? Macro coding
> is like this:
>
> Sub Macro1()
> Rows("4:20").Hidden = Not Rows("4:20").Hidden
> End Sub
> Sub Macro2()
> Rows("22:39").Hidden = Not Rows("22:39").Hidden
> End Sub
> Sub Macro3()
> Rows("40:54").Hidden = Not Rows("40:54").Hidden
> End Sub
> Sub Macro4()
> Rows("55:68").Hidden = Not Rows("55:68").Hidden
> End Sub
>
> The macro's noe get errors when i protect the sheet without allowing
> the rows to be edited. But that is not what I want. Hope you understand
> my case better and can help me to turn it around .. thanks for your
> input so far.
>
> Best regards,
> Hunter
>
>
> --
> huntermcg
> ------------------------------------------------------------------------
> huntermcg's Profile:
> http://www.excelforum.com/member.php...o&userid=19391
> View this thread: http://www.excelforum.com/showthread...hreadid=469683
>

[huntermcg]

It works a lot better now. Now errors.

Now, it only asks for the password before the macro's at the worksheet get to use. Is it possible to bypass that, since I already protected also the workbook itself by password?

[huntermcg]

anyone who can help me?

[STEVE BELL]

Time to start at the beginning. Many have already posted and you have
indicated that some of the stuff worked.

So - post your code in a reply. Explain at which point(s) you have
problems. And give us as much explanation as possible.

Include where the buttons are (regular module or sheet module, or
ThisWorkbook module.
What kind of buttons - from Forms toolbar or Control toolbar. And how these
buttons activate each macro.

Also it helps to maintain previous posts in your replys - this makes it
easier to follow what has already been posted.

You might consider sending me a copy of your workbook. (But be aware that I
am using Excel 2000 and not Excel 2003 - there may or may not be a conflict
between versions)

[email protected]
Remove "AYN" from email to respond

--
steveB

Remove "AYN" from email to respond
"huntermcg" <[email protected]> wrote
in message news:[email protected]...
>
> anyone who can help me?
>
>
> --
> huntermcg
> ------------------------------------------------------------------------
> huntermcg's Profile:
> http://www.excelforum.com/member.php...o&userid=19391
> View this thread: http://www.excelforum.com/showthread...hreadid=469683
>

[STEVE BELL]

And one more thing -
Make sure that Option Explicit appears at the top of all code modules.
This forces Excel to
notify you about most errors in the code. This helps to debug the code.
Compile your code
and check all the errors noted.

--
steveB

Remove "AYN" from email to respond
"huntermcg" <[email protected]> wrote
in message news:[email protected]...
>
> anyone who can help me?
>
>
> --
> huntermcg
> ------------------------------------------------------------------------
> huntermcg's Profile:
> http://www.excelforum.com/member.php...o&userid=19391
> View this thread: http://www.excelforum.com/showthread...hreadid=469683
>

[huntermcg]

I got the code in a regular Module, like this:

Sub Macro1()
ActiveSheet.Unprotect
Rows("4:20").Hidden = Not Rows("4:20").Hidden
ActiveSheet.Protect
End Sub

Not in a sheet or thisworkbook module.

The code is activated through a command button wich is assigned to the specific macro. thats it.

i only want to be able to hide/unhide the rows without having to protect/unprotect an fill i my password every time.

if we cannot work it out through the posts, i will maybe send you the sheet. but it is all very confidential info. i trust you understand.

[STEVE BELL]

Sounds like you are having problems with password

Just a modest change to get around that -

Sub Macro1()
ActiveSheet.Unprotect Password:="wxyz"
Rows("4:20").Hidden = Not Rows("4:20").Hidden
ActiveSheet.Protect Password:="wxyz"
End Sub

But you will need to password protect the VB code to prevent users from
discovering what the password is...


Let me know if this works.

If not - save-as your workbook (name = "Dummy"). Delete all sensitive data
and send that to me.
(this way you can send me the shell with all the works, but without the
data)

Or you can trust me - I am well versed in Confidentiality, and once was
cleared to "Secret".

--
steveB

Remove "AYN" from email to respond
"huntermcg" <[email protected]> wrote
in message news:[email protected]...
>
> I got the code in a regular Module, like this:
>
> Sub Macro1()
> ActiveSheet.Unprotect
> Rows("4:20").Hidden = Not Rows("4:20").Hidden
> ActiveSheet.Protect
> End Sub
>
> Not in a sheet or thisworkbook module.
>
> The code is activated through a command button wich is assigned to the
> specific macro. thats it.
>
> i only want to be able to hide/unhide the rows without having to
> protect/unprotect an fill i my password every time.
>
> if we cannot work it out through the posts, i will maybe send you the
> sheet. but it is all very confidential info. i trust you understand.
>
>
> --
> huntermcg
> ------------------------------------------------------------------------
> huntermcg's Profile:
> http://www.excelforum.com/member.php...o&userid=19391
> View this thread: http://www.excelforum.com/showthread...hreadid=469683
>

[huntermcg]

Super. It worked.

It sounds so simple. I need to know more about VBA, because it can so so much for XL.

[STEVE BELL]

Great!

Just keep monitoring this forum.

And get a copy of Ron's Google Search add-in
it's free and it helps find answers fast...

http://www.rondebruin.nl/Google.htm

--
steveB

Remove "AYN" from email to respond
"huntermcg" <[email protected]> wrote
in message news:[email protected]...
>
> Super. It worked.
>
> It sounds so simple. I need to know more about VBA, because it can so
> so much for XL.
>
>
> --
> huntermcg
> ------------------------------------------------------------------------
> huntermcg's Profile:
> http://www.excelforum.com/member.php...o&userid=19391
> View this thread: http://www.excelforum.com/showthread...hreadid=469683
>

[huntermcg]

hi.

i have another question wich you could help me with.

now the code is something like:
Sub VerbergenOP1FA()
ActiveSheet.Unprotect Password:="xyz"
Rows("7:13").Hidden = Not Rows("7:13").Hidden
ActiveSheet.Protect Password:="xyz"
End Sub

This code opens the rows and close them. now i want when i open the rows not to show all. for instance alone the rows 7:8 and 12:13. How do I adjust this in the VBA code.

Please help.

[STEVE BELL]

Sounds like you just want to show Rows("9:11")
If that is it - just change your code accordingly.

Or you can insert into your code additional lines to hide
these rows.

Other wise I'll need more details on just what you want to do.
--
steveB

Remove "AYN" from email to respond
"huntermcg" <[email protected]> wrote
in message news:[email protected]...
>
> hi.
>
> i have another question wich you could help me with.
>
> now the code is something like:
> Sub VerbergenOP1FA()
> ActiveSheet.Unprotect Password:="xyz"
> Rows("7:13").Hidden = Not Rows("7:13").Hidden
> ActiveSheet.Protect Password:="xyz"
> End Sub
>
> This code opens the rows and close them. now i want when i open the
> rows not to show all. for instance alone the rows 7:8 and 12:13. How do
> I adjust this in the VBA code.
>
> Please help.
>
>
> --
> huntermcg
> ------------------------------------------------------------------------
> huntermcg's Profile:
> http://www.excelforum.com/member.php...o&userid=19391
> View this thread: http://www.excelforum.com/showthread...hreadid=469683
>

[huntermcg]

No. You do not really understand me.

Ex.

I have a sheet in wich from row 1 :10, row 7 and 8 are hidden. Now, I have the command button/macro wich I use when I want to hide row 1:10 and unhide it back again when I click the button again. Only when I click the button again to unhide the rows, also row 7 and 8 show. That was not supposed to happen.

So, I need the code we made like this:

Sub VerbergenOP1FA()
ActiveSheet.Unprotect Password:="xyz"
Rows("1:10").Hidden = Not Rows("1:10").Hidden
ActiveSheet.Protect Password:="xyz"
End Sub

Only now with a line wich let row 7 and 8 hidden, when I want to unhide rows 1 tot 10 again.

I hope you understand and can make it possible to adjust he coding to this.