Using Toggle Button to Hide/Unhide blank rows

[gboudreau]

Ok, I have searched the forum, tried using Excel Help but just can't quite figure it out. What I have created is a project management timeline spreadsheet. what i want to do is use a toggle button to hide and unhide any rows that do not contain any information in column A. sometimes not all the rows will be used this depends on the amount of task for the project.

The following is what I have so far but just cant figure out the rest. If I click more than once ALL rows are hidden.

Private Sub ToggleButton1_Click()

Dim myRg As Range
'Change the [A1], [A65536] to your range
Set myRg = Range([A1].End(xlDown), [A80].End(xlUp))
On Error Resume Next
Set myRg = myRg.SpecialCells(4)
If Err = 0 Then
myRg.EntireRow.Hidden = True
Else
MsgBox "No blanks"
End If
Set myRg = Nothing

End Sub



Additionally, once I have this toggle working I would like to be able to protect the sheet and have the toggle still work.

Any ideas?

Thanks

 

[Sandcastor]

Sorry mine didnt work for you, be sure to change ToggleButton2 to whatever your toggle button is named (ToggleButton1, or whatever if you named it. I think that's where mine isn't working for you. And sorry you couldnt download the dummy file, skydive sucks. I'll post again later, I'm on my mobile now.

Rick, your script looks nice and simple, I'm going to try it next time I need to hide rows.

 

[gboudreau]

Rick,

Is there any other code that I should add. When I copy the code exactly as listed then the Toggle button does not do anything. It just moves in and out with each click. When I paste your code in then the VBS defaults to (General) in the top of the window. Again not a VBS wizard by far and this is the most I have ever dived into VBS. Again thanks for all the help.

 

[Rick Rothstein]

Rick,

Is there any other code that I should add. When I copy the code exactly as listed then the Toggle button does not do anything. It just moves in and out with each click. When I paste your code in then the VBS defaults to (General) in the top of the window. Again not a VBS wizard by far and this is the most I have ever dived into VBS. Again thanks for all the help.

Let's double check what I asked you earlier... the cells in range A19:A100 have data in them, right, there are no formulas in those cells? If that is true, then let me double check where you installed the code... you put it in a standard Module (Insert/Module on the VB editor menu bar), right? One more thing to check... your blank cells in the range A19:A100 are really blank (you aren't using a space character to make the cell blank), right? If all these conditions are true, then the code should work. If you still cannot get it to work, send me your workbook and I'll try to see what is causing the code to fail. My email address is rickDOTnewsATverizonDOTnet (replace the upper case letters with the symbol they spell).

 

[Sandcastor]

new link to dummy file, good luck: http://dl.dropbox.com/u/55504971/dummy.zip.
gboudreau, I think for both mine and Rick's script, you need to insert it in the correct spot. Right-Click your Toggle Button, and choose "View Code". This will bring up the VBA page and display something like:

Private Sub ToggleButton1_Click()

End Sub

just insert your code here, for either my script or Ricks, and you should have luck.

"Compile error: Variable not defined"

I think this may be because your toggle button is named something different, most likely ToggleButton1. After inserting code in the correct spot, be sure to rename all instances of ToggleButton2.

 

[Rick Rothstein]

I think for both mine and Rick's script, you need to insert it in the correct spot. Right-Click your Toggle Button, and choose "View Code". This will bring up the VBA page and display something like:

Private Sub ToggleButton1_Click()

End Sub

just insert your code here, for either my script or Ricks, and you should have luck.

You hit the nail right on the head... that was exactly what the problem was. The OP sent me his file and the problem was he just copy/pasted the code I posted into the worksheet module but didn't take the body of the code and insert it into the ToggleButton's click event. I sent him a working file which he should have received by now.

 

[gboudreau]

Thanks for the information. This will help in so many ways. I appreciate all the help.

 

[Sandcastor]

Hooray! I was making the same mistakes back when i was at only six posts!
Now the tie breaker: Do you use My code or Rick's Code? Juuuuust kidding. I don't want to know.

But I do want to know if your sheet works the way you want it to.
And if your head feels full from learning.

Also, keep copies of handy scripts like this in your personal.xlsm to use later.

 

[Sandcastor]

Wow Rick, your code is stellar. On the sheet I'm using now, since I don't need to replace with blank rows, your code is ideal... And fast. Thanks

 

[Rick Rothstein]

Wow Rick, your code is stellar. On the sheet I'm using now, since I don't need to replace with blank rows, your code is ideal... And fast. Thanks

Thanks for the nice words... I appreciate them. One thing to keep in mind with the code I posted... it only works if the cells are "truely" blank; that is, they do not contain formulas displaying the empty string ("")... that is a limitation of SpecialCells when looking for blanks.

 

[Sandcastor]

So if a cell contains a formula that returns a blank, it won't work? good to know. I slotted it in the "DATE" column of my form, so if a date isn't entered, the job didn't happen. Good to go.

Thanks again!