Dave P. Question! hide/Unhide

[dreamkeeper]

Hi Dave,
The code you gave me a while back works great fro hiding and unhiding
columns rows etc. Is there a way to use the same code but change it to
worksheets. I have a bout 15 worksheets that I would like to unhide in
different groups...similar to the column hide and unhide you helped me
with.

Here is the code you gave me...I am hoping it will be easy to
manipulate to worksheets.

thansk so much,
Tina
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

[Dave Peterson]

I'd use the code in the other thread, but modify the sheet names for each
button.

dreamkeeper wrote:
>
> Hi Dave,
> The code you gave me a while back works great fro hiding and unhiding
> columns rows etc. Is there a way to use the same code but change it to
> worksheets. I have a bout 15 worksheets that I would like to unhide in
> different groups...similar to the column hide and unhide you helped me
> with.
>
> Here is the code you gave me...I am hoping it will be easy to
> manipulate to worksheets.
>
> thansk so much,
> Tina
> 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

--

Dave Peterson

[Dave Peterson]

One more using just one macro...

But it depends on the name of each of the buttons (from the Forms toolbar).
Select the button and change the name so that it has a number at the end. I
used Button1, Button2, Button3, Button4, and Button5. The macro picks up that
last number and uses it to know which group of sheets to hide.

(ps. That "Dim RngToHide As Range" was left over from the first code--it's not
needed in this routine.)


Option Explicit
Sub HideUnhideSheets()

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

ReDim mySheets(1 To 5)
mySheets(1) = Array("sheet2", "sheet9", "sheet99")
mySheets(2) = Array("sheet3", "sheet6")
mySheets(3) = Array("sheet3", "sheet7", "sheet9")
mySheets(4) = Array("sheet2", "sheet4", "sheet6")
mySheets(5) = Array("sheet8", "sheet9")

With ActiveSheet
Set myBTN = .Buttons(Application.Caller)
BTNNumber = CLng(Right(myBTN.Name, 1))
End With

myVisible = Sheets(mySheets(BTNNumber)(LBound(mySheets(BTNNumber)))).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(BTNNumber)) To UBound(mySheets(BTNNumber))
Sheets(mySheets(BTNNumber)(iCtr)).Visible = myVisible
Next iCtr

End Sub


Dave Peterson wrote:
>
> I'd use the code in the other thread, but modify the sheet names for each
> button.
>
> dreamkeeper wrote:
> >
> > Hi Dave,
> > The code you gave me a while back works great fro hiding and unhiding
> > columns rows etc. Is there a way to use the same code but change it to
> > worksheets. I have a bout 15 worksheets that I would like to unhide in
> > different groups...similar to the column hide and unhide you helped me
> > with.
> >
> > Here is the code you gave me...I am hoping it will be easy to
> > manipulate to worksheets.
> >
> > thansk so much,
> > Tina
> > 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
>
> --
>
> Dave Peterson

--

Dave Peterson

[dreamkeeper]

Dave... thanks so much...I'll give it a try.
YOU are tryly a god-send!
Tina