Excel VBA unhide columns across multiple sheets with click in cell

[Stuepef]

I would like to add a macro to my workbook that unhides columns across 3 worksheets when I click in A1 on any of those worksheets. Here is what I have thus far:

Macro to Unhide columns across those sheets:

Sub OpenColumnsSummary()
Dim wsMySheet As Worksheet
   Application.ScreenUpdating = False
   For Each wsMySheet In Sheets(Array(Sheet6.Name, Sheet7.Name, Sheet8.Name))
      With wsMySheet
         .Columns("J:M").EntireColumn.Hidden = False
         .Columns("N:Q").EntireColumn.Hidden = False
         .Columns("R:U").EntireColumn.Hidden = False
         .Columns("V:Y").EntireColumn.Hidden = False
         .Columns("Z:AC").EntireColumn.Hidden = False
         .Columns("AD:AG").EntireColumn.Hidden = False
      End With
   Next wsMySheet
   Application.ScreenUpdating = True
End Sub

I am looking for some guidance on how I can execute this code when A1 is clicked on any of sheets 6, 7 or 8.

Thank you!

 

[Fluff]

Do you want the code to work on the sheet you click, on all three regardless of which was clicked?

 

[Stuepef]

@Fluff - I would like the macro to work on all worksheets regardless of which was clicked if possible.

 

[Fluff]

In that case try

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
   Application.ScreenUpdating = False
   If Target.Address(0, 0) <> "A1" Then Exit Sub
   Cancel = True
   If Sh.CodeName = "Sheet6" Or Sh.CodeName = "Sheet7" Or Sh.CodeName = "Sheet8" Then
      For Each wsMySheet In Sheets(Array(Sheet6.Name, Sheet7.Name, Sheet8.Name))
         With wsMySheet
            .Columns("J:M").EntireColumn.Hidden = False
            .Columns("N:Q").EntireColumn.Hidden = False
            .Columns("R:U").EntireColumn.Hidden = False
            .Columns("V:Y").EntireColumn.Hidden = False
            .Columns("Z:AC").EntireColumn.Hidden = False
            .Columns("AD:AG").EntireColumn.Hidden = False
         End With
      Next wsMySheet
   End If
   Application.ScreenUpdating = True
End Sub

This needs to go in the ThisWorkbook module & you will need to doubleclick A1

 

[Stuepef]

@Fluff - works great, thank you!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Stuepef]

@Fluff - Is it possible to add the same code except change the target to A4 to hide the same columns? I tried doing that myself within the ThisWorkBook, but when I update the code and sub name, it changes it to General instead of Workbook. Can I not have two codes in the ThisWorkBook? Here is what I attempted:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)   
Application.ScreenUpdating = False
   If Target.Address(0, 0) <> "A4" Then Exit Sub
   Cancel = True
   If Sh.CodeName = "Sheet6" Or Sh.CodeName = "Sheet7" Or Sh.CodeName = "Sheet8" Then
      For Each wsMySheet In Sheets(Array(Sheet6.Name, Sheet7.Name, Sheet8.Name))
         With wsMySheet
         .Columns("J:M").EntireColumn.Hidden = .Range("K20").Value = ""
         .Columns("N:Q").Hidden = .Range("O20").Value = ""
         .Columns("R:U").Hidden = .Range("S20").Value = ""
         .Columns("V:Y").Hidden = .Range("W20").Value = ""
         .Columns("Z:AC").Hidden = .Range("AA20").Value = ""
         .Columns("AD:AG").Hidden = .Range("AE20").Value = ""
         End With
      Next wsMySheet
   End If
   Application.ScreenUpdating = True
End Sub

 

[Fluff]

You cannot two event codes of the same type.
Try

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
   Application.ScreenUpdating = False
   If Sh.CodeName = "Sheet6" Or Sh.CodeName = "Sheet7" Or Sh.CodeName = "Sheet8" Then
      If Target.Address(0, 0) = "A1" Then
         Cancel = True
         For Each wsMySheet In Sheets(Array(Sheet6.Name, Sheet7.Name, Sheet8.Name))
            With wsMySheet
               .Columns("J:M").EntireColumn.Hidden = False
               .Columns("N:Q").EntireColumn.Hidden = False
               .Columns("R:U").EntireColumn.Hidden = False
               .Columns("V:Y").EntireColumn.Hidden = False
               .Columns("Z:AC").EntireColumn.Hidden = False
               .Columns("AD:AG").EntireColumn.Hidden = False
            End With
         Next wsMySheet
      ElseIf Target.Address(0, 0) = "A4" Then
         Cancel = True
         For Each wsMySheet In Sheets(Array(Sheet6.Name, Sheet7.Name, Sheet8.Name))
            With wsMySheet
            .Columns("J:M").EntireColumn.Hidden = .Range("K20").Value = ""
            .Columns("N:Q").Hidden = .Range("O20").Value = ""
            .Columns("R:U").Hidden = .Range("S20").Value = ""
            .Columns("V:Y").Hidden = .Range("W20").Value = ""
            .Columns("Z:AC").Hidden = .Range("AA20").Value = ""
            .Columns("AD:AG").Hidden = .Range("AE20").Value = ""
            End With
         Next wsMySheet
      End If
   End If
   Application.ScreenUpdating = True
End Sub

 

[Stuepef]

@Fluff - Thank you for the education and code - works exactly how I want it!

 

[Fluff]

Glad to help & thanks for the feedback