Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: VBA: Hide/Unhide Columns

  1. #1
    Join Date
    30th May 2003
    Posts
    70

    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
    Location
    USA
    Posts
    7,435
    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
    Posts
    70
    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
    Location
    Australia
    Posts
    31,718
    Hi Stefos

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

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

  5. #5
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310
    Stefos,

    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.

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

    Cheers
    Andy

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    30th May 2003
    Posts
    70
    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

Bookmarks

Posting Permissions

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