hide and show columns using one control button

[dreamkeeper]

Hi.
I am great in excel but not so great in macros

I am creating a rperot that has "this year", "plan", and "last year"
columns. I want to be able to hide and show specific columns like
"this year" by pressing a "hide ty" button and once it is hidden, have
that same button now say "show this year" and then show this year
columns.


I have created two button controled macros to do the above but I want
to only have one button that toggles and the text changes from hide to

show.


I have an example of this that I can send to someone.


thank you for your help...I am a rookie!
Sub Hide_TY()
Range("F:F,I:I").Select
Range("I1").Activate
Selection.EntireColumn.Hidden = True
End Sub


Sub unhide_TY()
Range("E1:G1,H1:J1").Select
Range("H1").Activate
Selection.EntireColumn.Hidden = False
Range("F10").Select
End Sub

 

[Dave Peterson]

I put a button from the Forms toolbar on that worksheet and assigned it this
macro:

Option Explicit
Sub HideUnhide()

Dim myBTN As Button
Dim RngToHide As Range

With ActiveSheet
Set myBTN = .Buttons(Application.Caller)
Set RngToHide = .Range("F:I")
End With

RngToHide.EntireColumn.Hidden = Not (RngToHide.Columns(1).Hidden)

If RngToHide.Columns(1).Hidden Then
myBTN.Caption = "Show This Year"
Else
myBTN.Caption = "Hide this Year"
End If
End Sub

You know that if you hide/unhide those columns manually, then the caption will
be out of sync.

 

[dreamkeeper]

Wow, Dave this is so great! Thank yo so much. Now if I can figure out
how to do this with the grouped outline show and hide, I wll have a
world class spreadsheet!

thank you so much!
Tina

 

[dreamkeeper]

HI Dave,
is there something I can put at the end of that code to autofit only
the visible cells?

thank you!
Tina

 

[Dave Peterson]

Option Explicit
Sub HideUnhide()

Dim myBTN As Button
Dim RngToHide As Range

With ActiveSheet
Set myBTN = .Buttons(Application.Caller)
Set RngToHide = .Range("F:I")
End With

RngToHide.EntireColumn.Hidden = Not (RngToHide.Columns(1).Hidden)

If RngToHide.Columns(1).Hidden Then
myBTN.Caption = "Show This Year"
Else
myBTN.Caption = "Hide this Year"
End If

On Error Resume Next
ActiveSheet.UsedRange.Cells _
.SpecialCells(xlCellTypeVisible).EntireColumn.AutoFit
On Error GoTo 0

End Sub

 

[dreamkeeper]

thank you Dave!

 

[dreamkeeper]

thank you Dave!

 

[dreamkeeper]

Hi Dave,
The macro you gave me works great! Is there a way to use the same
macro to change the range to sheets of the workbook. I would like to
use the same idea of using one control button to hide and show sheets
ina workbook.

Is there some wayt o change this code to do that? I want to hide and
show groups of sheets.
Thanks for your help.
Tina

 

[Dave Peterson]

Change the sheet names to what you want:

Option Explicit
Sub HideUnhideSheets()

Dim myBTN As Button
Dim mySheets As Variant
Dim myVisible As Long
Dim iCtr As Long

mySheets = Array("sheet2", "sheet9", "sheet99")

Dim RngToHide As Range

With ActiveSheet
Set myBTN = .Buttons(Application.Caller)
End With

myVisible = Sheets(mySheets(LBound(mySheets))).Visible

If myVisible = xlSheetVisible Then
myVisible = xlSheetHidden
myBTN.Caption = "Show the Sheets"
Else
myVisible = xlSheetVisible
myBTN.Caption = "Hide the sheets"
End If

For iCtr = LBound(mySheets) To UBound(mySheets)
Sheets(mySheets(iCtr)).Visible = myVisible
Next iCtr

End Sub

 

[dreamkeeper]

Hopefully this will be easy to fix but I got a run time
error...subscription out of range and the debugger takes me to :
myVisible = Sheets(mySheets(LBound(mySheets))).Visible

 

[dreamkeeper]

Dave,
I am using excel 2003 and I am still getting the subscription out of
range error...not sure what to fix. can you help?

Thanks,
Tina

 

[Dave Peterson]

I chose these names pretty much at random.

mySheets = Array("sheet2", "sheet9", "sheet99")

You'll have to change them to match what you need. If you did this already,
then I'd bet there was a typo.