Announcement

Collapse
No announcement yet.

VBA: Hide/Unhide Columns

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

  • #2
    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.
    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

    Comment


    • #3
      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.

      Comment


      • #4
        Hi Stefos

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

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

        Comment


        • #5
          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.

          Code:
          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.

          Cheers
          Andy

          Cheers
          Andy

          Comment


          • #6
            Hi Andy.
            thanks . I&#039;ve used the second option and worked fine.

            PS:Dave thanks too

            Comment

            Working...
            X