0

I have a macro that works perfectly well when triggered on an onchange event. It simply checks a cell value and hides or unhides rows elsewhere on the same active sheet. Here is the macro that hides or unhides rows:

Sub ToggleTaskTable()

MsgBox "Toggling Tasks"

        If Cells(56, 3).Value = "No" Then
            Cells(57, 1).EntireRow.Hidden = 
            MsgBox "Hding Rows 106, 148 and 190"

            ActiveSheet.Rows("106").Hidden = True
            ActiveSheet.Rows("148").Hidden = True
            ActiveSheet.Rows("190").Hidden = True

' try it another way

            Rows("106").Hidden = True
            Rows("148").Hidden = True
            Rows("190").Hidden = True

        Else
            Cells(57, 1).EntireRow.Hidden = False

           '  MsgBox "Showing task Rows 106, 148 and 190"

            Rows("106").Hidden = False
            Rows("148").Hidden = False
            Rows("190").Hidden = False
       End If
End Sub

If I call the macro as a result of changing a cell C56 to "No", it works perfectly.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = "$C$56" Then
      Call ToggleTaskTable
   End If
End Sub

If I call the macro from another macro, with C56 set to "No", it does not work at all.

Sub CallAllMacros()
   Call ToggleTaskTable
End Sub

Even though the msgbox shows indicating it is hiding the rows, it does not actually hide them.

I am totally stumped!

Community
  • 1
  • 1
Colin Houghton
  • 21
  • 3
  • I think you need to fully qualify your Objects and re-write your code. [You can start here on ways on how to do that](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). – L42 Sep 23 '14 at 23:23

1 Answers1

0
Sub ToggleTaskTable()
    With ActiveSheet
        .Range("A57,A106,A148,A190").EntireRow.Hidden = (.Cells(56, 3).Value = "No")
    End With
End Sub
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Thanks. This is much more eloquent than my new-be code, but did not solve the problem. My code had worked fine apparently, I had some subsequent code that re-unhid the rows. Duh! Thanks so much anyway :) – Colin Houghton Sep 24 '14 at 15:43