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

 

[Minx_Minxy]

Most welcome glad issue resolved & appreciate your feedback

Dave

Sorry, me again! The code did work on the test sheet I attached here on the post

But now, I have tried putting the code on the actual sheet I will be using and is not working

It stops at

 .TextFrame2.TextRange.Characters.Text = "Q" & i

Any ideas why?

Also, If I want to change the text of the shape to show Show/Hide Q1, Show/Hide Q2 and so forth.

I would then write it like this?

        .Name = "Show/Hide Q" & i
            .TextFrame2.TextRange.Characters.Text = "Show/Hide Q" & i

 

[dmt32]

S
I have tried putting the code on the actual sheet I will be using and is not working

It stops at

.TextFrame2.TextRange.Characters.Text = "Q" & i

what is the error being reported?

If I want to change the text of the shape to show Show/Hide Q1, Show/Hide Q2 and so forth.

I would then write it like this?
.Name = "Show/Hide Q" & i
.TextFrame2.TextRange.Characters.Text = "Show/Hide Q" & i

should be OK - try it.

Dave

 

[Minx_Minxy]

what is the error being reported?



should be OK - try it.

Dave

The error is runtime error '-2147024809(80070057)

 

[dmt32]

The error is runtime error '-2147024809(80070057)

cannot replicate that - are you able to place copy of the actual workbook (with dummy or no data) on a shared drive?

Dave

 

[Minx_Minxy]

cannot replicate that - are you able to place copy of the actual workbook (with dummy or no data) on a shared drive?

Dave

Unfortunately I cannot It has several other tabs, with very sensitive contents, I don't want to share what I shouldn't.

Do you have any ideas as to what the error could be? The sheets Operations and EHSQ has other shapes as well, they are links to external documents, but they are not named the same as the shapes I am working with.

 

[dmt32]

Unfortunately I cannot It has several other tabs, with very sensitive contents, I don't want to share what I shouldn't.

do not need any data just blank workbook

The sheets Operations and EHSQ has other shapes as well, they are links to external documents, but they are not named the same as the shapes I am working with.

That is likely to be the problem as SetButtonShapes code I assumed that they were only shapes on your worksheet.
If cannot provide a copy of workbook - can you provide a screenshot to let me see where you have placed the shapes the code needs to refer to?

Dave

 

[Minx_Minxy]

do not need any data just blank workbook


That is likely to be the problem as SetButtonShapes code I assumed that they were only shapes on your worksheet.
If cannot provide a copy of workbook - can you provide a screenshot to let me see where you have placed the shapes the code needs to refer to?

Dave

Sorry, I should have mentioned that! Is difficult when working with sensitive data. One from the operations tab and the other from EHSQ tab

 

[dmt32]

Can you place the shapes in column B on both sheets like the Operations sheet ?

Then try this update to the code & see if resolves

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
        If shp.TopLeftCell.Column = 2 Then
        i = i + 1
        With shp
            .Name = "Q" & i
            .TextFrame2.TextRange.Characters.Text = "Show/Hide Q" & i
            .OnAction = "'DisplayColumns """ & HideColumns.Areas(i).Address(0, 0) & """ '"
        End With
        End If
    Next shp
    Set HideColumns = Nothing
    i = 0
    Next sh
End Sub

Dave

 

[Minx_Minxy]

I am getting a new error, it says it cannot run the macro.

Below is link to a very redacted version of the file I am working with

Test2

 

[dmt32]

You appear to have some form of corruption with that workbook.

I copied one of the sheets to a new workbook & ran the code with no issues - if error persist for you then may be worthwhile you doing the same for your project.

I did ask that you place the four shapes in column B (as shown in image) before running the code - once this is complete, you can move the shapes to their required place.