Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 6 of 6

Thread: VBA: Hide/Unhide Columns

  1. #1
    Join Date
    30th May 2003

    VBA: Hide/Unhide Columns

    How can i use a checkBox to hide the same column (eg. Col AH) from sheet2 to sheet10 and return on sheet1 at cell A1, using tick/untick option?
    Can this work too with 5 checkboxes, each for hide/unhide one column, in previous condition?

    I think this would be in a array with sheets, but dont know exactly what to do.

    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    15th April 2003
    The attached workbook does what you ask. The code is attached to the first sheet and is activated when the checkbox is checked or not. The control is from the control toolbar. You can see the code by rightclicking on the sheet1 tab and slelecting View Code.

    Excel Video Tutorials / Excel Dashboards Reports

  3. #3
    Join Date
    30th May 2003
    Dear Derk,
    In attached workbook, your example works fine, but if every sheet's name are different, (e.g. sheet2=Jan, sheet3=Feb....sheet10=Dec, then how can i figure this out? tks again for your help.

    Excel Video Tutorials / Excel Dashboards Reports

  4. #4
    Join Date
    24th January 2003
    Hi Stefos

    Not sure what the code that Derk has given is, but the code below will loop through all Worksheets in the ActiveWorkbook

    Sub ForEachSheet()
    Dim wSheet As Worksheet
        For Each wSheet In Worksheets
            'YOUR CODE
        Next wSheet
    End Sub

  5. #5
    Join Date
    7th March 2003
    Essex, England

    Here are two variations to Derk's example.
    The first loop will change all worksheets apart from the active one.

    The second loop compares sheet name with a list you supply.

    Private Sub CheckBox1_Click()
        Dim i As Integer
        Application.ScreenUpdating = False
        ' all sheets except current
        For i = 1 To Worksheets.Count
            If Worksheets&#40;i&#41;.Name <> ActiveSheet.Name Then
                Worksheets&#40;i&#41;.Range&#40;"C1"&#41;.EntireColumn.Hidden = CheckBox1.Value
            End If
        Next i
        &#039; OR
        &#039; all named sheets
        For i = 1 To Worksheets.Count
            Select Case Worksheets&#40;i&#41;.Name
            Case "Jan", "Feb"  &#039; add your sheet names here
                Worksheets&#40;i&#41;.Range&#40;"C1"&#41;.EntireColumn.Hidden = CheckBox1.Value
            End Select
        Next i
        On Error Resume Next
        Application.ScreenUpdating = True
        Cells&#40;1, 1&#41;.Select
    End Sub
    Remember to comment out the loop you don&#039;t need.


    Excel Video Tutorials / Excel Dashboards Reports

  6. #6
    Join Date
    30th May 2003
    Hi Andy.
    thanks . I&#039;ve used the second option and worked fine.

    PS:Dave thanks too

    Excel Video Tutorials / Excel Dashboards Reports

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. hide/unhide columns using checkbox
    By tallmikerocks in forum EXCEL HELP
    Replies: 5
    Last Post: September 25th, 2012, 06:05
  2. Replies: 3
    Last Post: September 18th, 2011, 15:46
  3. Hide unhide columns
    By wotme in forum EXCEL HELP
    Replies: 10
    Last Post: November 12th, 2010, 22:45
  4. Hide / Unhide columns
    By Damo01 in forum EXCEL HELP
    Replies: 1
    Last Post: March 10th, 2006, 09:38
  5. Hide and unhide columns
    By Frederic74 in forum EXCEL HELP
    Replies: 2
    Last Post: September 15th, 2005, 15:07


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts