Toggle hidden rows with code

Topic

New Reply

I have a worksheet where I have manually hidden some rows if the value if column A (A1, a2, a3, etc) =0. Now I have created two buttons with assigned macros so I can unhide these rows or hide the rows. The code is below and it works fine.

Sub Unhide()
Rows(“5:30”).EntireRow.Hidden = False
End Sub

Sub hide()
Dim i As Integer
Application.ScreenUpdating = False
For i = 5 To 30
If Sheets(“planning”).Range(“A” & i).Value = “0” Then
Rows(i).EntireRow.Hidden = True
End If
Next i
Application.ScreenUpdating = True
End Sub

——
I would like to be able to combine both of these actions into the same button as a toggle which would mean changing the assigned macro or running code based on the value of a flag as well as changing the text of that button depending on the value of that flag – something like click to unhide or click to hide.

However, I don’t know how to do this. Any advice or ideas?

Reply | Quote

Replies

Is there a particular reason you aren’t just using an autofilter, as a matter of interest?

Reply | Quote

I had to correct my posting. Autofilter will work however, I wanted a slicker interface because I will likely be adding more to the macro/code later. – such as copying a range to the clipboard.

Is there a particular reason you aren’t just using an autofilter, as a matter of interest?

Reply | Quote

You could use code like this, substituting the correct name of the button:

Code:

Sub ToggleHide()
  Dim wsh As Worksheet
  Dim btn As Shape
  Dim i As Long
  Set wsh = Worksheets("Planning")
  Set btn = wsh.Shapes("Button 1")
  Application.ScreenUpdating = False
  If btn.TextFrame.Characters.Text = "Hide" Then
	For i = 5 To 30
	  If wsh.Range("A" & i).Value = "0" Then
		wsh.Rows(i).Hidden = True
	  End If
	Next i
	btn.TextFrame.Characters.Text = "Unhide"
  Else
	wsh.Range("A5:A30").EntireRow.Hidden = False
	btn.TextFrame.Characters.Text = "Hide"
  End If
  Application.ScreenUpdating = True
End Sub

Reply | Quote

Thanks, This is perfect.

You could use code like this, substituting the correct name of the button:

Code:

Sub ToggleHide()
  Dim wsh As Worksheet
  Dim btn As Shape
  Dim i As Long
  Set wsh = Worksheets("Planning")
  Set btn = wsh.Shapes("Button 1")
  Application.ScreenUpdating = False
  If btn.TextFrame.Characters.Text = "Hide" Then
[tab][/tab]For i = 5 To 30
[tab][/tab]  If wsh.Range("A" & i).Value = "0" Then
[tab][/tab][tab][/tab]wsh.Rows(i).Hidden = True
[tab][/tab]  End If
[tab][/tab]Next i
[tab][/tab]btn.TextFrame.Characters.Text = "Unhide"
  Else
[tab][/tab]wsh.Range("A5:A30").EntireRow.Hidden = False
[tab][/tab]btn.TextFrame.Characters.Text = "Hide"
  End If
  Application.ScreenUpdating = True
End Sub

Reply | Quote

I thought it was perfect until I encountered an unanticipated condition. Now I realize I need to hide the row ONLY IF the value of A, D, & G = 0.

Can I insert the AND (X,Y,Z) function into VB?

Something like AND (A1=0, D1=0, G1=0)

Thanks, This is perfect.

Reply | Quote

You can change the line

If wsh.Range(“A” & i).Value = “0” Then

to

If wsh.Range(“A” & i).Value = “0” And wsh.Range(“D” & i).Value = “0” And wsh.Range(“G” & i).Value = “0” Then

Reply | Quote

Thank you – I was trying something similar but it wasn’t working.

You can change the line

If wsh.Range(“A” & i).Value = “0” Then

to

If wsh.Range(“A” & i).Value = “0” And wsh.Range(“D” & i).Value = “0” And wsh.Range(“G” & i).Value = “0” Then

Reply | Quote

I know I have seen this before but cannot find it. How do I put the cursor in a particular cell after completion of a macro? In other words, after the code has run, go to K2 then endsub.

Thank you – I was trying something similar but it wasn’t working.

Reply | Quote

Add the line

Range(“K2”).Select

Reply | Quote

Thanks Hans.

Add the line

Range(“K2”).Select

Reply | Quote

Sorry to keep coming back but I find that when I protect the sheet, I am unable to select or click on the button object.

If I allow selecting locked and unlocked cells, then I can select and click the button but I get an error as it tries to run the macro. The error is

‘Unable to set the Hidden property of the Range class’

Any way to lock the sheet yet still be able to click the button and run the macro?

Thanks Hans.

Reply | Quote

If you tick the check box “Edit Objects” in the Protect Sheet dialog, the user will be able to click the button.

To make it possible to hide or unhide rows, you can add a line

ActiveSheet.Unprotect

at the beginning of the macro, and a line

ActiveSheet.Protect

at the end. If you set a password, you can add this to both lines, enclosed in quotes:

ActiveSheet.Unprotect “MyPassword”
…
Activesheet.Protect “MyPassword”

Reply | Quote

Do you mean to literally type “MyPassword” or to actually have the password in quotes?

If you tick the check box “Edit Objects” in the Protect Sheet dialog, the user will be able to click the button.

To make it possible to hide or unhide rows, you can add a line

ActiveSheet.Unprotect

at the beginning of the macro, and a line

ActiveSheet.Protect

at the end. If you set a password, you can add this to both lines, enclosed in quotes:

ActiveSheet.Unprotect “MyPassword”
…
Activesheet.Protect “MyPassword”

Reply | Quote

Do you mean to literally type “MyPassword” or to actually have the password in quotes?

You should replace MyPassword with the actual password, and enclose it in quotes. So if your password is reldaS_noD:

ActiveSheet.Protect “reldaS_noD”

Reply | Quote

Got it – thanks.

You should replace MyPassword with the actual password, and enclose it in quotes. So if your password is reldaS_noD:

ActiveSheet.Protect “reldaS_noD”

Reply | Quote