hide and show columns using one control button

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

0
3/2/2006 6:04:11 PM
excel 39879 articles. 2 followers. Follow

11 Replies
704 Views

Similar Articles

[PageSpeed] 50

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 wrote:
> 
> 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
0
petersod (12005)
3/2/2006 6:25:59 PM
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

0
3/2/2006 8:32:11 PM
HI Dave,
is there something I can put at the end of that code to autofit only
the visible cells?

thank you!
Tina

0
3/2/2006 10:00:09 PM
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 wrote:
> 
> 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
0
petersod (12005)
3/2/2006 10:46:14 PM
thank you Dave!

0
3/7/2006 5:36:21 PM
thank you Dave!

0
3/7/2006 6:14:03 PM
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

0
3/28/2006 7:42:38 PM
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 wrote:
> 
> 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
0
petersod (12005)
3/28/2006 8:56:11 PM
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

0
3/28/2006 10:45:34 PM
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

0
3/30/2006 5:52:59 PM
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.

dreamkeeper wrote:
> 
> 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
0
petersod (12005)
3/30/2006 6:04:09 PM
Reply:

Similar Artilces: