VBA to unhide Columns

[mbrooks1009]

Good morning,

Is it possible through VBA to unhide a column in one tab when you unhide another tab? Example: If I unhide tab 6, then unhide column 6 in tab 1.

Thanks

 

[Micron]

The only event I can think of for this is at the workbook level but it would fire every time the sheet is activated, which is what unhiding it will do. To unhide column 6 in sheet1 when Sheet9 is made visible:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "Sheet9" Then Sheets("Sheet1").Columns(6).EntireColumn.Hidden = False
End Sub

 

[mbrooks1009]

The only event I can think of for this is at the workbook level but it would fire every time the sheet is activated, which is what unhiding it will do. To unhide column 6 in sheet1 when Sheet9 is made visible:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "Sheet9" Then Sheets("Sheet1").Columns(6).EntireColumn.Hidden = False
End Sub

Micron,

Thank you, going to give this a try, will let you know. Greatly appreciate the response.

 

[Tupe77]

Do you mean something like this?

This code will unhide the desired column when the sheet status changes from hidden to visible.
And it will hide the desired column when the sheet status changes from visible to to hidden.

Copy this to ThisWorkbook

Option Explicit

' Create Dict for tracking Sheet status changes
Private Sub Workbook_Open()
    Call Module1.CreateDictFromSheets
End Sub


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
' Unhides a column when the sheet is unhidden.
    Dim Status As String: Status = Module1.SheetStatus(Sh.name)
    Dim OldStatus As String: OldStatus = dict(Sh.name)
    
    If Status <> OldStatus Then
        Debug.Print Sh.name & " Activated, status changed from " & OldStatus & " to " & Status
        dict(Sh.name) = Status
        Call Module1.UnhideColumn(Sh.name, Status)
    End If
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
' Hides the column when the sheet is hidden.
    Dim Status As String: Status = Module1.SheetStatus(Sh.name)
    Dim OldStatus As String: OldStatus = dict(Sh.name)
    
    If Status <> OldStatus Then
        Debug.Print Sh.name & " Deactivated, status changed from " & OldStatus & " to " & Status
        dict(Sh.name) = Status
        Call Module1.UnhideColumn(Sh.name, Status)
    End If
End Sub

Copy this to top of Module1

Option Explicit
Global dict As Object ' Create a persistent dictionary to track changes to the sheet.

Sub CreateDictFromSheets()
' Reads the state of the sheets into the dictionary.
Dim ws As Worksheet

Set dict = CreateObject("Scripting.Dictionary")
    For Each ws In ActiveWorkbook.Worksheets
        dict.Add ws.name, SheetStatus(ws.name)
    Next
End Sub

Function SheetStatus(name As String)
' Converts the value of Worksheets(name).Visible to a literal.
    Select Case Worksheets(name).Visible
    Case xlSheetHidden: SheetStatus = "Hidden"
    Case xlSheetVeryHidden: SheetStatus = "Very Hidden"
    Case Else: SheetStatus = "Visible"
    End Select
End Function

Sub UnhideColumn(wsName As String, Status As String)
' Defines which sheets affect which columns.
Dim wsData As Worksheet: Set wsData = Worksheets("Sheet1") '

If Status = "Hidden" Then ' The sheet is hidden so the column is also hidden.
    Select Case wsName
        Case "Sheet2": wsData.Columns(2).Hidden = True ' Case "Sheet2": Worksheets("Sheet4").Columns(2).Hidden = True 
        Case "Sheet3": wsData.Columns(3).Hidden = True
        Case "Sheet4": wsData.Columns(4).Hidden = True
        Case "Sheet5": wsData.Columns(5).Hidden = True
        Case "Sheet6": wsData.Columns(6).Hidden = True
        Case "Sheet7": wsData.Columns(7).Hidden = True
        Case Else:
    End Select
ElseIf Status = "Visible" Then ' The sheet is visible so the column is also visible.
    Select Case wsName
        Case "Sheet2": wsData.Columns(2).Hidden = False
        Case "Sheet3": wsData.Columns(3).Hidden = False
        Case "Sheet4": wsData.Columns(4).Hidden = False
        Case "Sheet5": wsData.Columns(5).Hidden = False
        Case "Sheet6": wsData.Columns(6).Hidden = False
        Case "Sheet7": wsData.Columns(7).Hidden = False
        Case Else:
    End Select
End If
End Sub

My apologies for any quirks, English is not my native language. "So I blame Google translate for all my goofs."

 

[Tupe77]

Improved version of my code from yesterday.
Automatically asks to reread sheet status if VBA is terminated.
Prevents annoying error message flood if sheet status information is lost.

Copy this to ThisWorkbook

Option Explicit

Private Sub Workbook_Open() ' Create Dict for tracking Sheet status changes
    Call Module1.CreateDictFromSheets
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object) ' Unhides a column when the sheet is unhidden.

If dict Is Nothing Then ' Ask to ReRead sheet status if state has lost
    Call Module1.SheetStatusLost_msgbox
End If

    Dim Status As String: Status = Module1.SheetStatus(Sh.name)
    Dim OldStatus As String: OldStatus = dict(Sh.name)
    
    If Status <> OldStatus Then
        Debug.Print Sh.name & " Activated, status changed from " & OldStatus & " to " & Status
        Call Module1.UnhideColumn(Sh, Status)
        dict(Sh.name) = Status
    End If
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) ' Hides the column when the sheet is hidden.

If dict Is Nothing Then ' Ask to ReRead sheet status if state has lost
    Call Module1.SheetStatusLost_msgbox
End If

    Dim Status As String: Status = Module1.SheetStatus(Sh.name)
    Dim OldStatus As String: OldStatus = dict(Sh.name)
    
    If Status <> OldStatus Then
        Debug.Print Sh.name & " Deactivated, status changed from " & OldStatus & " to " & Status
        Call Module1.UnhideColumn(Sh, Status)
        dict(Sh.name) = Status
    End If
End Sub

Copy this to TOP of Module1

Option Explicit
Global dict As Object ' Create a persistent dictionary to track changes to the sheet.

Sub CreateDictFromSheets() ' Reads the state of the sheets into the dictionary.
Dim ws As Worksheet

Set dict = CreateObject("Scripting.Dictionary")
    For Each ws In ActiveWorkbook.Worksheets
        dict.Add ws.name, SheetStatus(ws.name)
    Next
End Sub

Function SheetStatus(name As String) ' Converts the value of Worksheets(name).Visible to a literal.
    Select Case Worksheets(name).Visible
    Case xlSheetHidden: SheetStatus = "Hidden"
    Case xlSheetVeryHidden: SheetStatus = "Very Hidden"
    Case Else: SheetStatus = "Visible"
    End Select
End Function

Sub UnhideColumn(ws As Object, Status As String) ' Defines which sheets affect which columns.
Dim wsData As Worksheet: Set wsData = Worksheets("Sheet1")

' If the columns you want to hide are all on different sheets: 
' Case "Sheet2": Worksheets("Sheet4").Columns(2).Hidden = True
' = (if Current Sheet name is Sheet2 then hide Column 2 from Sheet4)
If Status = "Hidden" Then ' The sheet is hidden so the column is also hidden.
    Select Case ws.name ' Current Sheet name
        Case "Sheet2": wsData.Columns(2).Hidden = True 
        Case "Sheet3": wsData.Columns(3).Hidden = True 
        Case "Sheet4": wsData.Columns(4).Hidden = True
        Case "Sheet5": wsData.Columns(5).Hidden = True
        Case "Sheet6": wsData.Columns(6).Hidden = True
        Case "Sheet7": wsData.Columns(7).Hidden = True
        Case Else:
    End Select
ElseIf Status = "Visible" Then ' The sheet is visible so the column is also visible.
    Select Case ws.name
        Case "Sheet2": wsData.Columns(2).Hidden = False
        Case "Sheet3": wsData.Columns(3).Hidden = False
        Case "Sheet4": wsData.Columns(4).Hidden = False
        Case "Sheet5": wsData.Columns(5).Hidden = False
        Case "Sheet6": wsData.Columns(6).Hidden = False
        Case "Sheet7": wsData.Columns(7).Hidden = False
        Case Else:
    End Select
End If
End Sub

Sub SheetStatusLost_msgbox() ' Asks to reread the sheet status information if VBA execution has been interrupted and the status information has been lost.
    If MsgBox("Sheet state information lost." & vbCrLf & "VBA execution has probably been interrupted." & vbCrLf & "Should we reread the sheet data (Yes) or terminate execution (No)" & vbCrLf & "Termination may trigger several error messages!", vbYesNo + vbExclamation + vbDefaultButton1 + vbSystemModal, "Error in VBA") = vbYes Then
        Call Module1.CreateDictFromSheets
    Else
        End
    End If
End Sub

My apologies for any quirks, English is not my native language. "So I blame Google translate for all my goofs."