+ Reply to Thread
Results 1 to 2 of 2

Using VBA to Hide and Unhide Columns with Click of Button

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    07-17-2012
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    715

    Using VBA to Hide and Unhide Columns with Click of Button

    I would like a button to be pressed that would hide specific columns. Once the button is pressed again, the columns appear.

    Sub Hide()
    '
    ' Macro1 Macro
    '
    
    '
    
    If ActiveSheet.Shapes.Range(Array("Button 1")).Caption = "Teamwise Dashboard Hide" Then
      Range("I:X").EntireColumn.Hidden = True
      ActiveSheet.Shapes.Range(Array("Button 1")).Caption = "Teamwise Dashboard Show"
    Else
      Range("I:X").EntireColumn.Hidden = False
      ActiveSheet.Shapes.Range(Array("Button 1")).Caption = "Hide Information"
    End If
        
    End Sub
    This is what I was attempting to use. I would like the indicated columns (I:X) to hide upon pressing the button, then all of the columns to show again once pressed again. Ideally, the button could also be labeled to show "Hide Information" and "Show Information" correctly.

    But I get run time error 438' Object doesnt support this property or method. on below line..

    If ActiveSheet.Shapes.Range(Array("Button 1")).Caption = "Teamwise Dashboard Hide" Then
    Where am I failing here?

    Thanks,
    Shiva
    Last edited by shiva_reshs; 09-03-2014 at 03:12 PM.
    Keep the Forum clean :


    1. Use [ code ] code tags [ /code ]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    2. Show appreciation to those who have helped you by clicking * Add Reputation below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

  2. #2
    Forum Contributor
    Join Date
    07-17-2012
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    715

    Re: Using VBA to Hide and Unhide Columns with Click of Button

    Sub Hide()
    
    If Sheets("Dash Board").Shapes("Button 1").TextFrame.Characters.Text = "Teamwise Dashboard Hide" Then
      Range("I:X").EntireColumn.Hidden = True
      Sheets("Dash Board").Shapes("Button 1").TextFrame.Characters.Text = "Teamwise Dashboard Show"
    ElseIf Sheets("Dash Board").Shapes("Button 1").TextFrame.Characters.Text = "Teamwise Dashboard Show" Then
      Range("I:X").EntireColumn.Hidden = False
      Sheets("Dash Board").Shapes("Button 1").TextFrame.Characters.Text = "Teamwise Dashboard Hide"
    End If
        
    End Sub
    This one worked.

    Thanks all for looking into this..

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Hide And Unhide Column With One Button,,,,Once Click Hide,,,,,Again Click Unhide
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-11-2014, 07:58 AM
  2. [SOLVED] VBA macro to hide/unhide columns depending on button text
    By namso1902 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-09-2013, 08:12 AM
  3. button to toggle hide/unhide columns
    By dbuff in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-03-2013, 07:56 PM
  4. Hide/Unhide Multiple Sets of Columns with Toggle Button
    By Mischief433 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-06-2012, 05:55 AM
  5. hide and unhide columns with a button
    By Pig in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 02-24-2009, 07:38 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1