VBA Hide/Unhide Columns, different/multiple sheets

[Minx_Minxy]

I have two sheets, "Operation" and "EHSQ", I want to be able to hide and unhide certain columns on these sheets, I am thinking of using a a shape and assign the macro where the user can press, like Q1, Q2, Q3 etc. So the user hits Q1, if the columns are unhidden, then the macro hides it and vice versa.

The columns on each sheets are different:

I need the macro to:

1) make it work to hide and unhide when user hit the shape with the macro assigned
2) make it work not only in the Operations sheet, but for EHSQ as well

This is what I got so far

Code for Operations

Sub Q1_Hide
  Columns("H:J").Select
    Selection.EntireColumn.Hidden = True
End Sub

Sub Q2_Hide
Columns("K:M").Select
    Selection.EntireColumn.Hidden = True
End Sub

Sub Q3_Hide
Columns("N:P").Select
    Selection.EntireColumn.Hidden = True
End Sub

Sub Q4_Hide
Columns("Q:S").Select
    Selection.EntireColumn.Hidden = True
End Sub
 
SubQ1_UnHide
  Columns("H:J").Select
    Selection.EntireColumn.Hidden = False
End Sub

SubQ2_UnHide
Columns("K:M").Select
    Selection.EntireColumn.Hidden = False
End Sub

SubQ3_UnHide
Columns("N:P").Select
    Selection.EntireColumn.Hidden = False
End Sub

SubQ4_Unhide
Columns("Q:S").Select
    Selection.EntireColumn.Hidden = False
End Sub

This one is for EHSQ:

SubQ1_Hide
  Columns("G:I").Select
    Selection.EntireColumn.Hidden = True
End Sub

SubQ2_Hide
Columns("J:L").Select
    Selection.EntireColumn.Hidden = True
End Sub

SubQ3_Hide
Columns("M:O").Select
    Selection.EntireColumn.Hidden = True
End Sub

SubQ4_Hide
Columns("P:R").Select
    Selection.EntireColumn.Hidden = True
End Sub
 
SubQ1_UnHide
  Columns("G:I").Select
    Selection.EntireColumn.Hidden = False
End Sub

SubQ2_UnHide
Columns("J:L").Select
    Selection.EntireColumn.Hidden = False
End Sub

SubQ3_UnHide
Columns("M:O").Select
    Selection.EntireColumn.Hidden = False
End Sub

SubQ4_Unhide
Columns("P:R").Select
    Selection.EntireColumn.Hidden = False
End Sub

 

[Joe4]

I will show you the first one, the rest all follow the same pattern:

Sub Q1_Hide()
  ActiveSheet.Columns("H:J").EntireColumn.Hidden = Not ActiveSheet.Columns("H:J").EntireColumn.Hidden
End Sub

Note: Be sure to save all these procedures in a General VBA module, and not in one of the Sheet VBA modules, if you want to be able to access it from any sheet.

 

[Minx_Minxy]

I will show you the first one, the rest all follow the same pattern:

Sub Q1_Hide()
  ActiveSheet.Columns("H:J").EntireColumn.Hidden = Not ActiveSheet.Columns("H:J").EntireColumn.Hidden
End Sub

Note: Be sure to save all these procedures in a General VBA module, and not in one of the Sheet VBA modules, if you want to be able to access it from any sheet.

Thank you!


I did not understand your note. I do need the macro to work on the Operations sheet and another in the EHSQ sheet, because the ranges are different in each sheet.

 

[Joe4]

"General" modules are also sometimes referred to as "Standard" modules.
See the "Inserting a module of form into your code" section of this link here for home to create these: What is a VBA Module?

If you insert the VBA code there, it can be referenced from ANY sheet in your workbook.
And note how the VBA code I posted uses "ActiveSheet"? That means when it runs, it will run against whatever sheet you have open at the time (the "Active" sheet).

 

[dmt32]

I do need the macro to work on the Operations sheet and another in the EHSQ sheet, because the ranges are different in each sheet.

Hi,
if you are using shapes as your buttons then you should not need to assign them to individual procedures (macro) One common code should be able to do what you want

Give following a try & see if does what you want

1 - ensure that on each sheet that you have inserted 4 shapes you want to use as buttons

2 - Place BOTH following codes in STANDARD module (from VBA Editor Insert > Module) and place codes there.

Sub DisplayColumns(ByVal HideAddress As String)
    Dim ColumnsShow As Range,
  
    Set ColumnsShow = ActiveSheet.Range(HideAddress)
  
    Application.ScreenUpdating = False
  
    ColumnsShow.EntireColumn.Hidden = Not ColumnsShow.EntireColumn.Hidden
  
    Application.ScreenUpdating = True
  
End Sub

Sub SetButtonShapes()
    Dim shp         As Shape
    Dim HideColumns As Range
    Dim i           As Long, a As Long
    Dim sh          As Worksheet
 
    For Each sh In ThisWorkbook.Worksheets(Array("Operation", "EHSQ"))
     a = a + 1
   
    Set HideColumns = sh.Range(Choose(a, "H:J,K:M,N:P,Q:S", "G:I,J:L,M:O,P:R"))
  
    For Each shp In sh.Shapes
        i = i + 1
        With shp
            .Name = "Q" & i
            .TextFrame2.TextRange.Characters.Text = "Q" & i
            .OnAction = "'DisplayColumns """ & HideColumns.Areas(i).Address(0, 0) & """ '"
        End With
      
    Next shp
    Set HideColumns = Nothing
    i = 0
    Next sh
End Sub

3 - code SetButtonShapes you just need to run once. This will assign each shape to the common code for both sheets performing the following
- name the shape
- add caption (Q1, Q2) etc
' - assign Onction for each shape to the common code

You will note that the Onaction passes an argument to the common code for the range address the shape is to Hide / Unhide

Hopefully, when you press a shape it will Hide / Unhide the assigned range

Hope Helpful

Dave

 

[Minx_Minxy]

Hi,
if you are using shapes as your buttons then you should not need to assign them to individual procedures (macro) One common code should be able to do what you want

Give following a try & see if does what you want

1 - ensure that on each sheet that you have inserted 4 shapes you want to use as buttons

2 - Place BOTH following codes in STANDARD module (from VBA Editor Insert > Module) and place codes there.

Sub DisplayColumns(ByVal HideAddress As String)
    Dim ColumnsShow As Range,
 
    Set ColumnsShow = ActiveSheet.Range(HideAddress)
 
    Application.ScreenUpdating = False
 
    ColumnsShow.EntireColumn.Hidden = Not ColumnsShow.EntireColumn.Hidden
 
    Application.ScreenUpdating = True
 
End Sub

Sub SetButtonShapes()
    Dim shp         As Shape
    Dim HideColumns As Range
    Dim i           As Long, a As Long
    Dim sh          As Worksheet
 
    For Each sh In ThisWorkbook.Worksheets(Array("Operation", "EHSQ"))
     a = a + 1
  
    Set HideColumns = sh.Range(Choose(a, "H:J,K:M,N:P,Q:S", "G:I,J:L,M:O,P:R"))
 
    For Each shp In sh.Shapes
        i = i + 1
        With shp
            .Name = "Q" & i
            .TextFrame2.TextRange.Characters.Text = "Q" & i
            .OnAction = "'DisplayColumns """ & HideColumns.Areas(i).Address(0, 0) & """ '"
        End With
     
    Next shp
    Set HideColumns = Nothing
    i = 0
    Next sh
End Sub

3 - code SetButtonShapes you just need to run once. This will assign each shape to the common code for both sheets performing the following
- name the shape
- add caption (Q1, Q2) etc
' - assign Onction for each shape to the common code

You will note that the Onaction passes an argument to the common code for the range address the shape is to Hide / Unhide

Hopefully, when you press a shape it will Hide / Unhide the assigned range

Hope Helpful

Dave

Thank you Dave,

I have added the 4 shapes named Q1, Q2, etc., and entered the code in the module but it says subscript is put of range on this line

For Each sh In ThisWorkbook.Worksheets(Array("Operations", "EHSQ"))
a = a + 1

 

[Minx_Minxy]

Thank you Dave,

I have added the 4 shapes named Q1, Q2, etc., and entered the code in the module but it says subscript is put of range on this line

For Each sh In ThisWorkbook.Worksheets(Array("Operations", "EHSQ"))
a = a + 1

Here is a copy of the document

Loading Google Sheets

 

[dmt32]

Thank you Dave,

I have added the 4 shapes named Q1, Q2, etc., and entered the code in the module but it says subscript is put of range on this line

For Each sh In ThisWorkbook.Worksheets(Array("Operations", "EHSQ"))
a = a + 1

I suspect you have a space at the end of one or both the tab names - try checking that first & then re-run the code

Dave

 

[Minx_Minxy]

I suspect you have a space at the end of one or both the tab names - try checking that first & then re-run the code

Dave

You are right on point Dave

There was a space on named tab. Macro runs fine now, thank you very much.

 

[dmt32]

You are right on point Dave

There was a space on named tab. Macro runs fine now, thank you very much.

Most welcome glad issue resolved & appreciate your feedback

Dave