Detect Hidding Event ?

[e-me]

Hi,

I'm searching a way to detect when the user is hidding or showing a row or a column, could anyone help me ?

Thanks in advance.

[Carim]

Hi,

There is no event per say to capture hidding or showing ...
You would have to think about the context to find a solution ...probably with selection_change ...

[e-me]

There is no event per say to capture hidding or showing ...
You would have to think about the context to find a solution ...probably with selection_change ...

They haven't think to raise en event when excel shows or hides a row or a column .!

What do you think exactly?

[antoka05]

You can try with this code:

Sub Macro1()
   Dim lastRow As Long
   Dim inputSheet As String
   Dim outputSheet As String
   Dim found As String
   Dim countRow As Long
   Dim columnText As String
   Dim p1 As Integer
   
   countRow = 1
   lastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   'sheet to examine
   inputSheet = "sheet1"
   
   'sheet where to put data (found columns or rows hidden)
   outputSheet = "sheet4"
   
   'if not exist output sheet will add
   On Error Resume Next
   found = ""
   found = ThisWorkbook.Sheets(outputSheet).Name
   If found = "" Then
      Sheets.Add.Name = outputSheet
      Sheets(outputSheet).Move After:=Sheets(Sheets.Count)
   End If
   On Error GoTo 0
   Sheets(outputSheet).Cells.ClearContents
   
   'look for hidden rows
   Sheets(outputSheet).Cells(countRow, 1) = _
      "Hidden rows in sheet " & inputSheet & ":"
   countRow = countRow + 1
   For r = 1 To lastRow
      If Sheets(inputSheet).Rows(r).Hidden = True Then
         Sheets(outputSheet).Cells(countRow, 1) = r
         countRow = countRow + 1
      End If
   Next
   
   'look for hidden columns
   countRow = countRow + 1
   Sheets(outputSheet).Cells(countRow, 1) = _
      "Hidden columns in sheet " & inputSheet & ":"
   countRow = countRow + 1
   For c = 1 To Sheets(inputSheet).Columns.Count
      If Sheets(inputSheet).Columns(c).Hidden = True Then
         columnText = Columns(c).Address(False, False)
         p1 = InStr(columnText, ":")
         columnText = Left(columnText, p1 - 1)
         Sheets(outputSheet).Cells(countRow, 1) = columnText
         
         countRow = countRow + 1
      End If
   Next
   
End Sub

I hope it's what you need.

Regards,
Antonio

[e-me]

I don't really understand your code : this is only for reading hidden columns and rows, isn't it? The code can't react when a row or a column is hidden?

[e-me]

Any ideas, please?