hide rows with macro/togglebutton

H

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 t
unhide the rows. I could make to button's to assign each macro to, bu
that would be a lot of work, since I want to add this function t
several places in my worksheet.

A togglebutton would do the trick, but I do not know how to assign th
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 th
best solution. The only thing is that I have to put the togglebutto
somewhere outside the rows I wish to hide/unhide. If there is a bette
solution thinkable I am happy to hear ?

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

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
 
H

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?
 
H

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

huntermcg

I do not understand. I did that.

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

Bob Phillips

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

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 ca
read in a former post. then i put a commandbutton on the sheet an
assigned the macro to the button. thats it. then i only want to secur
the sheet again and he gives the error.

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

huntermcg

Hello Bob or any PRO reading this,

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

Best regards, Hunter
 
S

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
 
H

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
 
S

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

huntermcg

It works a lot better now. Now errors.

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

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)

(e-mail address removed)
Remove "AYN" from email to respond
 
S

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

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

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

huntermcg

Super. It worked.

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

peculiar condition: form optionbuttons lose position and linkedcellon row hide 1
VB Code to hide rows 3
Hidden rows 1
Need Help with a Macro 3
Macro for hiding/unhiding a set of rows 3
Macro to hide rows below 1
Hide and Unhide Rows button 6
Can you hide and unhide rows with a macro based on content of colu 4

Top