• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 834
  • Last Modified:

Need Excel button to hide/unhide columns

I want to give my users a way to switch between different ways to 'view' their worksheet.

Ideally I'd like a toolbar button that when clicked would hide certain columns, or if they are already hidden, unhide them.

Any help would be appreciated.

0
ksander
Asked:
ksander
  • 4
  • 4
  • 2
  • +2
1 Solution
 
sturobinson17Commented:
You could create 2 marco's. and two buttons. the first macro could be a recording of the columns being hidden, which would be played pack when button 1 is pressed. The second macro would be a recording of the columns being unhidden, which would be played back when button 2 is pressed. just an idea. :-)
0
 
ksanderAuthor Commented:
That's sort of what I had in mind, except with one button and one macro - the macro would test, if the columns were hidden it would unhide, and vice versa.
0
 
tureCommented:
ksander,

Perhaps you should consider a non-VBA approach for this.

Take a look at the View manager in the View (perhaps that is not the exact wording - I'm on a Swedish version of Excel now)

It is also possible to add an easy-to-use dropdown to help switching between views. Tools - Customize - Commands tab, In the Views category, drag the "View manager" listbox to any of your toolbars.

Ture Magnusson
Karlstad, Sweden
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
jklmnCommented:
Hi ksander,

Assume you need to hide/unhide column 2, try put following code to your button event:

    With Sheet1.Columns(2).EntireColumn
    .Hidden = Not .Hidden
    End With
0
 
criCommented:
If you do not have too many of these columns, use the Data|Group&Outline feature. If your worksheet is protected (as any distributed workbook should be), there is way to enable the group/ungroup on a protected worksheet. If interested, please ask.
0
 
ksanderAuthor Commented:
jklmn - It doesn't work ("unable to set the hidden property of the range class") because the sheet is protected.

When I unprotect the sheet nothing happens when I call the macro as written.

I tried replacing "Sheet1" with the true sheet name but get an "object required" error.  The macro should work on whatever the current sheet is.

But I guess the main thing is that it must work while the sheet is protected.  
-----------------------
ture - I did that and it works but not well.
1. Wherever it's called, the 'view' I created insists on jumping to it's 'home' sheet. I need it to show the same view (hiding the same columns) in all sheets.
2. If the workbook is closed and saved while a sheet is in a custom view, ir reopens in the same view.  It want to return to 'normal'.
-------------------------
cri - if Data/Group&Outline is the route to take, please tell me more.
--------------------------

One thing that may be a problem with what I want is that the I put an icon or control on the Web bar ,the only one visible, it's gone next time I open the sheet.

0
 
jklmnCommented:
Hi ksander,

The code is working in my Excel sheet with no protected, it should work for you as well.....but you need the sheet protected.....I got no idea at the moment :-(
0
 
tureCommented:
ksander,

If your code is run from a button on a worksheet, you may need to activate the active cell (kind of stupid) to avoid errors.

To hide/unhide columns with a protected sheet, you must unprotect it first. The best way is to protect it with the option UserInterfaceOnly:=True. This way, your code can work with the sheet but it is still protected from the user.

Sub View1()
 
  'Make sure that a cell is active
  ActiveCell.Activate
   
  'Protect the sheet only for manual editing.
  'The sheet can still be edited by VBA code.
  ActiveSheet.Protect Password:="carrot", UserInterfaceOnly:=True
 
  'Unhide all columns
  ActiveSheet.Columns.Hidden = False
 
  'Hide some of the columns
  ActiveSheet.Range("A:A,C:H,AA:AZ").EntireColumn.Hidden = True

End Sub

Sub View2()
 
  'Make sure that a cell is active
  ActiveCell.Activate
   
  'Protect the sheet only for manual editing.
  'The sheet can still be edited by VBA code.
  ActiveSheet.Protect Password:="carrot", UserInterfaceOnly:=True
   
  'Unhide all columns
  ActiveSheet.Columns.Hidden = False
 
  'Hide some of the columns
  ActiveSheet.Range("J:Z").EntireColumn.Hidden = True

End Sub

Ture Magnusson
Karlstad, Sweden
0
 
ksanderAuthor Commented:
Sorry ture, I don't understand.
 
1) View1 & View2 - are they identical except for which columns are hidden ?
2) Are you saying to use View1 to hide one group of columns and View2 to hide another - each with its own button ?
3) I don't see any sort of a toggle or an If test to see if the routine needs to hide or unhide.
4) I don't understand why the Protect statement is in the same code to hide or unhide columns. Assuming the sheet is already protected (as it is with an Auto_Open), is it necessary to re-protect with UserInterface.=True ? Or could I include that statement in Auto_Open.

Thanks

 
0
 
tureCommented:
1) Yes.
2) Yes.
3) Correct. My procedures don't have a toggle or check for hidden/unhidden columns.
4) It would be good to put the protection, with UserInterfaceOnly:=True, in an Auto_Open procedure.

In the procedure below, I have removed the protect statement, as I assume that you have it in your Auto_Open procedure. I have also added "toggle functionality" to switch between hiding/unhiding columns.

Sub ToggleView()

 'Declare a variable
 Dim IsHidden As Boolean

 'Make sure that a cell is active
 ActiveCell.Activate
   
 'Check Hidden state of one of the columns
 IsHidden = ActiveSheet.Range("C:C").EntireColumn.Hidden

 'Change hidden state of some columns
 ActiveSheet.Range("C:H,AA:AZ").EntireColumn.Hidden = Not IsHidden

End Sub

/Ture
0
 
ksanderAuthor Commented:
Perfect - thanks very much
0
 
tureCommented:
You are welcome, ksander! Glad it worked!

/Ture
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now