Results 1 to 10 of 10

VBA to Unhide and Hide Columns

This is a discussion on VBA to Unhide and Hide Columns within the Excel Questions forums, part of the Question Forums category; Hi all, I want to create two buttons.. one name HIDE and the other UNHIDE. What I need hide button ...

  1. #1
    New Member
    Join Date
    Oct 2002
    Posts
    7

    Default

    Hi all,

    I want to create two buttons.. one name HIDE and the other UNHIDE.

    What I need hide button to do when i click it is hide the 4 columns to the right (not always going to be columns b:e)

    For the unhide button unhide the 4 columns to the right (not always going to be columns b:e)

    any help would be appreciated?

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    67,298

    Default

    Activate the Control ToolBox and add a CommandButton to your worksheet. Right click the button and choose View Code. Paste this into the window on the right:

    Code:
    Private Sub CommandButton1_Click()
        CommandButton1.TopLeftCell.Offset(0, 1).Resize(, 4).EntireColumn.Hidden = True
    End Sub
    Not you will only need to add the middle line to the existing procedure stub. Press Alt+F11 to return to your worksheet and click the Design icon on the Control ToolBox.

    I will leave you to work out how to add the UnHide button.

  3. #3
    New Member
    Join Date
    Oct 2002
    Posts
    7

    Default

    Looks like i didn't describe my problem completely.

    I have another macro i run each day that copies the data from sheet1 to sheet2 and then hide all the columns in sheet2 except for the date column... basically everyday i run the update macro it inserts new columns and hide so i basically end up seeing just date columns with the data columns being hidden in between. I want to be able to copy the button each time i run the update marco and have it stay within the date column and only unhide or hide the fixed amount of columns to the right of it.

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    67,298

    Default

    Sounds like you can do this in your copy macro, without the need for a button.

    Post the code.

  5. #5
    New Member
    Join Date
    Oct 2002
    Posts
    7

    Default

    Sub DataRollover()
    '
    ' DataRollover Macro
    ' Macro recorded 10/22/02 by David T. Tran
    '
    ' Keyboard Shortcut: Ctrl+z
    '
    Sheets("Sheet2").Select
    Columns("B:J").Select
    Selection.Copy
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Application.CutCopyMode = False
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=Sheet1!R[2]C"
    Range("B2").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("C3").Select
    ActiveCell.FormulaR1C1 = "=Sheet1!R[5]C[-1]"
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "=Sheet1!R[5]C[-1]"
    Range("E3").Select
    ActiveCell.FormulaR1C1 = "=Sheet1!R[5]C[-1]"
    Range("F3").Select
    ActiveCell.FormulaR1C1 = "=Sheet1!R[5]C[-1]"
    Range("G3").Select
    ActiveCell.FormulaR1C1 = "=Sheet1!R[5]C[-1]"
    Range("H3").Select
    ActiveCell.FormulaR1C1 = "=Sheet1!R[5]C[-1]"
    Range("I3").Select
    ActiveCell.FormulaR1C1 = "=Sheet1!R[5]C[-1]"
    Range("J3").Select
    ActiveCell.FormulaR1C1 = "=Sheet1!R[5]C[-1]"
    Range("C3:D3").Select
    Selection.AutoFill Destination:=Range("C3:D86"), Type:=xlFillDefault
    Range("C3:D86").Select
    ActiveWindow.SmallScroll Down:=-75
    Range("E3").Select
    Selection.AutoFill Destination:=Range("E3:E86")
    Range("E3:E86").Select
    Range("F3").Select
    Selection.AutoFill Destination:=Range("F3:F86")
    Range("F3:F86").Select
    Range("G3").Select
    Selection.AutoFill Destination:=Range("G3:G86")
    Range("G3:G86").Select
    Range("H3").Select
    Selection.AutoFill Destination:=Range("H3:H10")
    Range("H3:H10").Select
    Range("H10").Select
    Selection.Copy
    Range("H11:H86").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWindow.SmallScroll Down:=-69
    Range("I3").Select
    Selection.AutoFill Destination:=Range("I3:I86")
    Range("I3:I86").Select
    Range("J3").Select
    Selection.AutoFill Destination:=Range("J3:J86")
    Range("J3:J86").Select
    Range("C3:J86").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Sheets("Sheet1").Select
    Range("B8:F91").Select
    Selection.ClearContents
    Range("H7:I91").Select
    Range("H91").Activate
    Selection.ClearContents
    Range("A5").Select
    Sheets("Sheet2").Select
    Columns("L:R").Select
    ActiveWindow.SmallScroll ToRight:=9
    Columns("L:S").Select
    Selection.EntireColumn.Hidden = True
    ActiveWindow.SmallScroll ToRight:=-9
    Columns("C:J").Select
    Selection.EntireColumn.Hidden = True
    Sheets("Sheet1").Select
    Range("A7").Select
    End

    I want to be able to copy the Hide and Unhide button into Sheet2 Column B each time i run this macro.

  6. #6
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    67,298

    Default

    Why don't you manually put the button in cell A1 of Sheet2 and change the code to:

    CommandButton1.TopLeftCell.Offset(0, 2).Resize(, 4).EntireColumn.Hidden = True

    That would probably be quicker because I always get an irresistible urge to completely rewrite recorded macros.

  7. #7
    New Member
    Join Date
    Oct 2002
    Posts
    7

    Default

    because lets say on Oct 27th I wants to see my hidden data on Oct 10th.. i know i can just high betwene the two dates but if i had a button to do it it'll be great.

  8. #8
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    67,298

    Default

    This code will add a button in cell B1 on Sheet2. Clicking the button will alternately hide/unhide the four columns to the right.

    Code:
    Sub AddButton()
        Dim Top As Single
        Dim Left As Single
        Dim Width As Single
        Dim Height As Single
        Dim Btn As Button
        Top = Sheets("Sheet2").Cells(1, 2).Top
        Left = Sheets("Sheet2").Cells(1, 2).Left
        Width = Sheets("Sheet2").Cells(1, 2).Width
        Height = Sheets("Sheet2").Cells(1, 2).Height * 2
        Set Btn = Sheets("Sheet2").Buttons.Add(Left, Top, Width, Height)
        With Btn
            With .Characters
                .Text = "Hide/" & Chr(10) & "Unhide"
                With .Font
                    .Name = "Arial"
                    .FontStyle = "Bold"
                    .Size = 8
                End With
            End With
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .OnAction = "ToggleHide"
        End With
    End Sub
    
    Sub ToggleHide()
        Dim BtnName As String
        Dim Rng As Range
        BtnName = Application.Caller
        Set Rng = ActiveSheet.Buttons(BtnName).TopLeftCell.Offset(0, 1).Resize(, 4).EntireColumn
        Rng.Hidden = Not Rng.Hidden
    End Sub

  9. #9
    Board Regular
    Join Date
    Sep 2002
    Posts
    106

    Default

    excuse the intrusion - how would you (or where would you) modify the code to instead of hiding the 4 columns, hide/unhide columns based on their names? any hints/tips would be appreciated. If you dont mind - i may stand on the shoulder of giants and use your code if i can adapt it for my purpose - thanks.

  10. #10
    New Member
    Join Date
    Jan 2012
    Posts
    4

    Default Re: VBA to Unhide and Hide Columns

    Hi All,

    I have learnt so much from the people who have shared they knowledge in this website and thank you so much.

    I need the below macro to be edit like, every second column should be hide.

    for example: when i click the button the col's B, D,F,G.... should hid showing col's A, C, D....

    Thanks for ur help in advance.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com