Hide Column with multiple command buttons

[arisomar]

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.

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

 

[HighAndWilder]

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.

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.

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