Hide Column with multiple command buttons

A

arisomar

New Member
Joined
Sep 18, 2024
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi All,

First time poster, so go easy :). Our company has a stupidly long contract progress spread sheet on one tab. This contains information pertaining to Contracts, Sales, Procurement, Technical and Health & Safety.

I am trying to set up 5 command buttons that hide various columns in order to show only pertinent information for that particular department

I can get button 1 to work in isolation by using the code:

--------------------------------------------------------------
Private Sub CommandButton1_Click()
If CommandButton1.Caption = "Sales" Then
Range("D:D, O:S").EntireColumn.Hidden = True
CommandButton1.Caption = "Sales Only"
Else
Range("D:D, O:S").EntireColumn.Hidden = False
CommandButton1.Caption = "Sales"
End If
End Sub
--------------------------------------------------------------

Here is an example of what I am trying to achieve:

Example:

Button 1 toggles cells A:D

Button 2 toggles cell B:G and S:X but cancels out any command initiated by buttons 1 or 3.

Button 3 toggles cells A:B and F:G but cancels out any command initiated by buttons 1 or 2.

Hope this makes sense and fingers crossed is achievable. Below is the buttons I am trying to set up. All are Command buttons.

1726655392308.png


If someone can point me in the right direction, I would be ever so grateful. Thanks in advance.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
arisomar said:
Hi All,

First time poster, so go easy :). Our company has a stupidly long contract progress spread sheet on one tab. This contains information pertaining to Contracts, Sales, Procurement, Technical and Health & Safety.

I am trying to set up 5 command buttons that hide various columns in order to show only pertinent information for that particular department

I can get button 1 to work in isolation by using the code:

--------------------------------------------------------------
Private Sub CommandButton1_Click()
If CommandButton1.Caption = "Sales" Then
Range("D:D, O:S").EntireColumn.Hidden = True
CommandButton1.Caption = "Sales Only"
Else
Range("D:D, O:S").EntireColumn.Hidden = False
CommandButton1.Caption = "Sales"
End If
End Sub
--------------------------------------------------------------

Here is an example of what I am trying to achieve:

Example:

Button 1 toggles cells A:D

Button 2 toggles cell B:G and S:X but cancels out any command initiated by buttons 1 or 3.

Button 3 toggles cells A:B and F:G but cancels out any command initiated by buttons 1 or 2.

Hope this makes sense and fingers crossed is achievable. Below is the buttons I am trying to set up. All are Command buttons.

View attachment 116988

If someone can point me in the right direction, I would be ever so grateful. Thanks in advance.
Click to expand...

This is one procedure that just displays the required columns and is called from the procedure behind each button.

You will need to add the command button procedures for the other buttons.

I have added code for an 'All Columns' button.

I'm not sure what columns buttons 4 and 5 display.

VBA Code: 
Private Sub CommandButton1_Click()

     Call subDisplayColumns("Sales")

End Sub

Private Sub CommandButton2_Click()

     Call subDisplayColumns("Contracts")

End Sub

Public Sub subDisplayColumns(strGroup As String)
Dim Ws As Worksheet

  Application.ScreenUpdating = False

  Set Ws = ActiveSheet

  Ws.Cells.EntireColumn.Hidden = True
  
  With Ws
    
    Select Case strGroup
    
      Case "Sales":
      
        .Range("A:D").EntireColumn.Hidden = False
      
      Case "Contracts":
        
        .Range("B:G,S:X").EntireColumn.Hidden = False
      
      Case "Technical":
     
        .Range("A:B,F:G").EntireColumn.Hidden = False
      
      Case "H&S":
      
       .Range("A:D").EntireColumn.Hidden = False
      
      Case "Procurement":
      
      .Range("A:D").EntireColumn.Hidden = False
      
      Case "All Columns":
      
        Ws.Cells.EntireColumn.Hidden = False
      
    End Select
  
  End With
  
  Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
HighAndWilder said:
This is one procedure that just displays the required columns and is called from the procedure behind each button.

You will need to add the command button procedures for the other buttons.

I have added code for an 'All Columns' button.

I'm not sure what columns buttons 4 and 5 display.

VBA Code: 
Private Sub CommandButton1_Click()

     Call subDisplayColumns("Sales")

End Sub

Private Sub CommandButton2_Click()

     Call subDisplayColumns("Contracts")

End Sub

Public Sub subDisplayColumns(strGroup As String)
Dim Ws As Worksheet

  Application.ScreenUpdating = False

  Set Ws = ActiveSheet

  Ws.Cells.EntireColumn.Hidden = True
 
  With Ws
   
    Select Case strGroup
   
      Case "Sales":
     
        .Range("A:D").EntireColumn.Hidden = False
     
      Case "Contracts":
       
        .Range("B:G,S:X").EntireColumn.Hidden = False
     
      Case "Technical":
    
        .Range("A:B,F:G").EntireColumn.Hidden = False
     
      Case "H&S":
     
       .Range("A:D").EntireColumn.Hidden = False
     
      Case "Procurement":
     
      .Range("A:D").EntireColumn.Hidden = False
     
      Case "All Columns":
     
        Ws.Cells.EntireColumn.Hidden = False
     
    End Select
 
  End With
 
  Application.ScreenUpdating = True
   
End Sub
Click to expand...
Hi,

Thank you for responding. Apologies it has taken so long to get back to you. Work always gets in the way!

I tried the following:


Private Sub CommandButton1_Click()

Call subDisplayColumns("Sales")

End Sub

Private Sub CommandButton2_Click()

Call subDisplayColumns("Contracts")

End Sub

Private Sub CommandButton3_Click()

Call subDisplayColumns("Technical")

End Sub

Private Sub CommandButton4_Click()

Call subDisplayColumns("H&S")

End Sub

Private Sub CommandButton5_Click()

Call subDisplayColumns("Procurement")

End Sub

Private Sub CommandButton6_Click()

Call subDisplayColumns("All Columns")

End Sub

Public Sub subDisplayColumns(strGroup As String)
Dim Ws As Worksheet

Application.ScreenUpdating = False

Set Ws = ActiveSheet

Ws.Cells.EntireColumn.Hidden = True

With Ws

Select Case strGroup

Case "Sales":

.Range("A:D").EntireColumn.Hidden = False

Case "Contracts":

.Range("E:E,F:F").EntireColumn.Hidden = False

Case "Technical":

.Range("G:G,H:H").EntireColumn.Hidden = False

Case "H&S":

.Range("I:I").EntireColumn.Hidden = False

Case "Procurement":

.Range("J:J").EntireColumn.Hidden = False

Case "All Columns":

Ws.Cells.EntireColumn.Hidden = False

End Select

End With

Application.ScreenUpdating = True

End Sub


I added an 'All Columns' button:

1727347929537.png


I get runtime error:

1727347965796.png


Hit debug:
1727347991954.png


I literally have no clue 🤣

Any further help would be greatly appreciated.
 
Upvote 0
arisomar said:
Hi,

Thank you for responding. Apologies it has taken so long to get back to you. Work always gets in the way!

I tried the following:


Private Sub CommandButton1_Click()

Call subDisplayColumns("Sales")

End Sub

Private Sub CommandButton2_Click()

Call subDisplayColumns("Contracts")

End Sub

Private Sub CommandButton3_Click()

Call subDisplayColumns("Technical")

End Sub

Private Sub CommandButton4_Click()

Call subDisplayColumns("H&S")

End Sub

Private Sub CommandButton5_Click()

Call subDisplayColumns("Procurement")

End Sub

Private Sub CommandButton6_Click()

Call subDisplayColumns("All Columns")

End Sub

Public Sub subDisplayColumns(strGroup As String)
Dim Ws As Worksheet

Application.ScreenUpdating = False

Set Ws = ActiveSheet

Ws.Cells.EntireColumn.Hidden = True

With Ws

Select Case strGroup

Case "Sales":

.Range("A:D").EntireColumn.Hidden = False

Case "Contracts":

.Range("E:E,F:F").EntireColumn.Hidden = False

Case "Technical":

.Range("G:G,H:H").EntireColumn.Hidden = False

Case "H&S":

.Range("I:I").EntireColumn.Hidden = False

Case "Procurement":

.Range("J:J").EntireColumn.Hidden = False

Case "All Columns":

Ws.Cells.EntireColumn.Hidden = False

End Select

End With

Application.ScreenUpdating = True

End Sub


I added an 'All Columns' button:

View attachment 117339

I get runtime error:

View attachment 117340

Hit debug:
View attachment 117341

I literally have no clue 🤣

Any further help would be greatly appreciated.
Click to expand...
I've tried to replicate the problem on my copy and I can't, which is puzzling.

This line is run when any button is pressed and works fine when any button except (All Columns) is pressed.

Is that your experience?

The order in which the buttons are pressed is not important.
 
Upvote 0
Hi

Give following a try & see if it will do what you want
  • Make a back-up of your workbook & then DELETE all existing codes
  • Place following codes in a STANDARD module

Code: 
Enum GroupNames
    Sales = 1
    HealthAndSafety = 1
    Procurement = 1
    Contracts = 2
    Technical = 3
    ShowAllColumns = 4
End Enum

Sub DisplayColumns(ByVal vGroup As GroupNames, Optional ByVal sh As Object)
    Dim ColumnsShow As Range, rng As Range
    
    If sh Is Nothing Then Set sh = ActiveSheet
    
    Set rng = sh.Range("A:D,B:G,S:X, A:B,F:G")
    If vGroup > 4 Then vGroup = 4
    Set ColumnsShow = Choose(vGroup, rng.Areas(1), _
                                     Union(rng.Areas(2), rng.Areas(3)), _
                                     Union(rng.Areas(4), rng.Areas(5)), _
                                     rng)
    
    Application.ScreenUpdating = False
    
    rng.EntireColumn.Hidden = True
    
    ColumnsShow.EntireColumn.Hidden = False
    
    Application.ScreenUpdating = True
    
End Sub

The elements of the Enum type are initialized to constant values which are used to pass to the common procedure.
Enum Type displays the IntelliSense when accessed to ensure you pass correct value to the procedure.

1727365360555.png



Update your commandbutton codes with following

Code: 
Private Sub CommandButton1_Click()
    Call DisplayColumns(Sales)
End Sub

Private Sub CommandButton2_Click()
    Call DisplayColumns(Contracts)
End Sub

Private Sub CommandButton3_Click()
    Call DisplayColumns(Technical)
End Sub

Private Sub CommandButton4_Click()
    Call DisplayColumns(HealthAndSafety)
End Sub

Private Sub CommandButton5_Click()
    Call DisplayColumns(Procurement)
End Sub

Private Sub CommandButton6_Click()
    Call DisplayColumns(ShowAllColumns)
End Sub

I have only lightly tested but hopefully solution will do what you want


Dave
 
Upvote 0
Solution
HighAndWilder said:
I've tried to replicate the problem on my copy and I can't, which is puzzling.

This line is run when any button is pressed and works fine when any button except (All Columns) is pressed.

Is that your experience?

The order in which the buttons are pressed is not important.
Click to expand...
Hi,

Same error for any button 😥
 
Upvote 0
dmt32 said:
Hi

Give following a try & see if it will do what you want
  • Make a back-up of your workbook & then DELETE all existing codes
  • Place following codes in a STANDARD module

Code: 
Enum GroupNames
    Sales = 1
    HealthAndSafety = 1
    Procurement = 1
    Contracts = 2
    Technical = 3
    ShowAllColumns = 4
End Enum

Sub DisplayColumns(ByVal vGroup As GroupNames, Optional ByVal sh As Object)
    Dim ColumnsShow As Range, rng As Range
   
    If sh Is Nothing Then Set sh = ActiveSheet
   
    Set rng = sh.Range("A:D,B:G,S:X, A:B,F:G")
    If vGroup > 4 Then vGroup = 4
    Set ColumnsShow = Choose(vGroup, rng.Areas(1), _
                                     Union(rng.Areas(2), rng.Areas(3)), _
                                     Union(rng.Areas(4), rng.Areas(5)), _
                                     rng)
   
    Application.ScreenUpdating = False
   
    rng.EntireColumn.Hidden = True
   
    ColumnsShow.EntireColumn.Hidden = False
   
    Application.ScreenUpdating = True
   
End Sub

The elements of the Enum type are initialized to constant values which are used to pass to the common procedure.
Enum Type displays the IntelliSense when accessed to ensure you pass correct value to the procedure.

View attachment 117361


Update your commandbutton codes with following

Code: 
Private Sub CommandButton1_Click()
    Call DisplayColumns(Sales)
End Sub

Private Sub CommandButton2_Click()
    Call DisplayColumns(Contracts)
End Sub

Private Sub CommandButton3_Click()
    Call DisplayColumns(Technical)
End Sub

Private Sub CommandButton4_Click()
    Call DisplayColumns(HealthAndSafety)
End Sub

Private Sub CommandButton5_Click()
    Call DisplayColumns(Procurement)
End Sub

Private Sub CommandButton6_Click()
    Call DisplayColumns(ShowAllColumns)
End Sub

I have only lightly tested but hopefully solution will do what you want


Dave
Click to expand...
Hi,

That seems to work without any errors. However, trying to understand the code without any success.

Just to see an easy break down, how could I adjust the code to do the following:

Sales: Hide columns A-D
Procurement: Hide columns E-H
Contracts: Hide columns I-L
Technical: Hide columns M-P
Health and Safety: Hide columns Q-T

Just seeing how that is presented should allow me to tweak the code to hide desired columns.

Thank you for your help so far.
 
Upvote 0
arisomar said:
Hi,

That seems to work without any errors.
Click to expand...
Firstly, does the solution resolve your original requirement?


arisomar said:
Just to see an easy break down, how could I adjust the code to do the following:

Sales: Hide columns A-D
Procurement: Hide columns E-H
Contracts: Hide columns I-L
Technical: Hide columns M-P
Health and Safety: Hide columns Q-T
Click to expand...

Is this in addition to existing code? or a separate requirement as if so, it is the reverse of your original post and to accommodate, I would need to understand your project fully & as original solution likely to need some significant alteration.

If you can share copy of your workbook (with dummy data) on a file sharing site like Dropbox & provide a link to it here will take a further look.

Dave
 
Upvote 0
dmt32 said:
Firstly, does the solution resolve your original requirement?




Is this in addition to existing code? or a separate requirement as if so, it is the reverse of your original post and to accommodate, I would need to understand your project fully & as original solution likely to need some significant alteration.

If you can share copy of your workbook (with dummy data) on a file sharing site like Dropbox & provide a link to it here will take a further look.

Dave
Click to expand...
I have deleted all data but just left column headers. Thanks for the assist.

R801 Contract Progress Register (WIP 2 - with buttons) - Macro enabled.xlsm

1 file sent via WeTransfer, the simplest way to send your files around the world
we.tl we.tl
 
Upvote 0
arisomar said:
I have deleted all data but just left column headers. Thanks for the assist.
Click to expand...

You did not confirm if posted solution resolved your original request & if so, you should mark it as solution?

Sample workbook contains no code? - you need to clarify if this additional requirement is for a different workbook or an update to include with your first request?

Dave
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
VBA Code to Apply an AVERAGE of a Range on Cells as New Records are added Via a User Form
Replies
2
Views
72
Denny57
D
D
  • Question Question
Possible Use of CountIF and AVERAGE Functions
Replies
1
Views
130
Denny57
D
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
J
  • Solved
Creating a VBA toggle button to show/hide multiple adjacent and non-adjacent columns in Excel
Replies
4
Views
1K
dmt32
D
B
  • Solved
Using Command Button to hide & unhide selected Rows
Replies
5
Views
4K
Fluff
Fluff

Forum statistics

Threads
1,223,886
Messages
6,175,194
Members
452,616
Latest member
intern444

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top