Hide/unhide rows with buttons

[tracktor]

I am using several toggle buttons to hide/show rows of data related to the specific toggle buttons. I also want to hide and show the toggle buttons as well. When I try to do that the data underneath the toggle buttons is hidden but the toggle buttons don't hide with the cells.

A solution for the problem that I am having would be to have the ability to click on a cell as if it were a toggle button and hide/show rows of data when clicked.

OR

If there is a way to hide/show buttons as well would work too.

Your help is greatly appreciated.

 

[johnlucasprice]

Re: Hide/unhide rows or columns with buttons

Good suggestion. I'll see what I can do. Detail: I made the following macro's to hide groups of columns. (They unprotect the sheet, hide or show columns, scroll back to the top left, freeze the panes where I want them to, then protect the sheet again.) I have one for hide and one for protect. Any way to include your proposed solution using these macro's? (Attach them both to a single BUTTON?)

Sub Hide_Detail()
'
' Hide_Detail Macro
' Macro recorded 9/18/2009 by johnlucasprice
'

'
ActiveSheet.Unprotect Password:="password1"
Columns("S:AE").Select
Selection.EntireColumn.Hidden = True
Columns("AG:AT").Select
Selection.EntireColumn.Hidden = True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingRows:=True, Password:="password1"
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
Range("F9").Select
ActiveWindow.FreezePanes = True
Range("B2").Select
End Sub

Sub Show_Detail()
'
' Show_Detail Macro
' Macro recorded 9/18/2009 by johnlucasprice
'

'
ActiveSheet.Unprotect Password:="password1"
Selection.EntireColumn.Hidden = True
Columns("B:BC").Select
Range("BB1").Activate
Selection.EntireColumn.Hidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingRows:=True, Password:="password1"
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
Range("F9").Select
ActiveWindow.FreezePanes = True
Range("B2").Select
End Sub

 

[ZVI]

Hi & welcome to the Board, John!

Try:

Sub HideShowDetails()
  ' We needn't to protect/unprotect if using UserInterfaceOnly:=True
  ActiveSheet.Protect Password:="password1", UserInterfaceOnly:=True
  ' Hide/Unhide columns relative to the current state of S column
  If Columns("S").Hidden Then
    Columns("B:BC").Hidden = False
    Application.StatusBar = "Unhidden"
  Else
    Range("S1:AE1,AG1:AT1").EntireColumn.Hidden = True
    Application.StatusBar = "Hidden"
  End If
  ' Common part of code
  Application.ScreenUpdating = False
  ActiveWindow.FreezePanes = False
  Range("B2").Select
  ActiveWindow.ScrollRow = 1
  ActiveWindow.ScrollColumn = 1
  Range("F9").Select
  ActiveWindow.FreezePanes = True
  Range("B2").Select
  Application.ScreenUpdating = True
End Sub

Vladimir

 

[johnlucasprice]

You're a smooth operator! Worked perfectly. Thanks a megabyte!