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.
What am I doing wrong or missing?
Please Login or Register to view this content.
This has been driving me crazy for the past couple weeks, and have looked everywhere for an answer.
Thanks.
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
Please Login or Register to view this content.
Please help by:
Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know
There are 10 kinds of people in this world... those who understand binary, and those who don't.
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
Best wishes and have a nice day!
I see some other experts helped you already, anyway I had this for you in order to help you out:
Hope that help you!Please Login or Register to view this content.
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.
Last edited by The Phoenix; 07-13-2015 at 12:04 PM.
Could you upload a copy of the workbook?
Didn't realize it was activex:
Please Login or Register to view this content.
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.
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.
If you start writing.. let's say in cell BA1 "FALSE" and you you use this code:
Should work to match your code.Please Login or Register to view this content.
However, the solution that Arkadi is giving you is way more organized and structured. You should go with that one!
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.
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
GENIUS!!!! IT WORKS!!! UNBELIEVABLE!
This has been driving me CRAZY!
THANK YOU SO MUCH! YOU ROCK!!!!
Happy to help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks