Created this toggel button, but it's not working. Please help!

[The Phoenix]

I wanted to create the following toggle button.
Button would say 'Hide Done' and when clicked, it would hide all rows with the letter 'a' in cell in column B.
Same button would now say 'Show Done', and when clicked again, it would show the rows again.

Here's the code I came up with, but it's not working.

Please Login or Register  to view this content.

What am I doing wrong or missing?
This has been driving me crazy for the past couple weeks, and have looked everywhere for an answer.

Thanks.

[mikerickson]

Cross Post
http://www.mrexcel.com/forum/excel-q...ease-help.html

[Arkadi]

Please Login or Register  to view this content.

[Rioran]

Hello, Phoenix!

What as for syntax, you need to close all "For Each cell" sentences with "Next cell" expressions.

For basic Idea, you need to somehow differ - when button click will do "Hide" moves and when "Show" moves. For example, you may place somewhere a special cell with 1 or 0 value and do things according to it. You may differ cases with "Select Case" statement.

And just to consider, several tips from "best practices":

1). If you do "ActiveSheet.Unprotect" action - may be your code needs to invert it with "ActiveSheet.Protect" in the end.
2). Range("$B:$B") is not the most optimal link for "For Each" cycle. I recommend to limit observing area. For example, you may declare another Integer variable (Let it be X) to find the last possible row of your data and then use range like this: Range("B1:B" & X)
3). Using Application.ScreenUpdating = False is very tricky. It could be better to off updating before all invisible actions and get it back after with Application.ScreenUpdating = True

[amartinez988]

I see some other experts helped you already, anyway I had this for you in order to help you out:

Please Login or Register  to view this content.

Hope that help you!

[The Phoenix]

Thank you.
I tried assigning the code above to Active X button, but it does not work.
What am I missing?
I tried both amartinez and Arkadi's to no avail.
Thank you so much for all the responses, I greatly appreciate it.

[amartinez988]

Could you upload a copy of the workbook?

[Arkadi]

Didn't realize it was activex:

Please Login or Register  to view this content.

[The Phoenix]

toggletest.xlsm
OK, here it is with code above attached.
It doesn't have to be an ActiveX button. I could be a regular button, but as you can see, text doesn't show up on button and it doesn't hide or unhide done rows when clicked.

I know I must be doing something wrong. Have to admit, I cannot wait to see what it is.

Again, thank you soooo much for all your help. I will make sure to keep adding to your reputation.

Thanks.

[Arkadi]

Phoenix, my last post should work with activeX button... it doesn't do anything right now because the caption is CommandButton1 and my code works with caption 1 or 2, to figure out if it should hide or show.... so since no rows are hidden, start by giving the button the caption "Show Done" and then try again, it ought to work.

[amartinez988]

If you start writing.. let's say in cell BA1 "FALSE" and you you use this code:

Please Login or Register  to view this content.

Should work to match your code.

[amartinez988]

However, the solution that Arkadi is giving you is way more organized and structured. You should go with that one!

[The Phoenix]

Hello Arkadi,

Thanks for the response. Excuse my ignorance as I am relatively new at this, but how do I give a button a caption "Show Done"? Where would I type that?

Thanks.

[Arkadi]

if you right click the activeX button, click on properties, you get a list... 7th item or so is Caption... change CommandButton1 to Show Done

[The Phoenix]

GENIUS!!!! IT WORKS!!! UNBELIEVABLE!
This has been driving me CRAZY!

THANK YOU SO MUCH! YOU ROCK!!!!

[Arkadi]

Happy to help