Trigger a macro to run on specific sheets, not working?-CodePudding

Regarding this question about trigger event when Row or Column is hidden Link The provided answer applies to the whole workbook, but I need to adapt it to run only on specific sheets (same workbook). I tried the following modification, but it runs on the whole workbook also.

Sub Row_Hide_Macro(control As IRibbonControl, ByRef CancelDefault)
Dim ws As Worksheet
  For Each ws In ThisWorkbook.Worksheets
    If ws.name = "Sheet1" Or ws.name = "Sheet2" Then
     CancelDefault = True
 Else
     CancelDefault = False
    End If
   Next
End Sub

I also tried this code

Sub Row_Hide_Macro(control As IRibbonControl, ByRef CancelDefault)
  If Not Intersect(ThisWorkbook.Worksheets("Sheet1"), ThisWorkbook.Worksheets("Sheet2")) Is Nothing Then
         CancelDefault = True
     Else
         CancelDefault = False
     End If
End Sub

Error raised, Type mismatch on Intersect line As always, in advance great thanks for any help.

CodePudding user response：

It looks to me like you're not checking a specific sheet name in this code.

In the For loop you are iterating through all worksheets and updating the value of CancelDefault. I suspect that you want to only update CancelDefault based on the name of the sheet that you hid a row or column on.

Try this (untested):

Sub Row_Hide_Macro(control As IRibbonControl, ByRef CancelDefault)

    If ActiveWorkbook.ActiveSheet.Name = "Sheet1" Or ActiveWorkbook.ActiveSheet.Name = "Sheet2" Then
         CancelDefault = True
    Else
         CancelDefault = False
    End If
       
End Sub

Here you only check the name of the sheet that had a change made to it, and update the value of CancelDefault based on the name of that single worksheet.