Excel: EntireColumn.Hidden doesn't work in Worksheet_Change()


Hi there, this is my first post as I've been tweaking and hunting for a couple of days and I haven't been able to find a resolution here or elsewhere. Using Excel 2010 I am editing an existing unprotected workbook and have created EntireColumn.Hidden and EntireRow.Hidden commands in the Worksheet_Change() event to fire when a Data Validation cell is changed, but they don't work.

Private Sub Worksheet_Change(ByVal Target As Range)
With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    If Not Intersect(Target, Range("$C$2")) Is Nothing Then
        Select Case Target.Value
            Case "NO"
                MsgBox "You just changed to HIDE"          '<= Proves it fires
                Range("$C$3").Value = "Invisible"          '<= Does change cell
                Columns("N:O").EntireColumn.Hidden = True  '<= Doesn't hide
            Case "YES"
                MsgBox "You just changed to UNHIDE"        '<= Proves it fires
                Range("$C$3").Value = "Visible"            '<= Does change cell
                Columns("N:O").EntireColumn.Hidden = False '<= Doesn't unhide
        End Select
    End If
    
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub
The event is firing OK as I have msgboxes to prove it, and I can change cell values etc. with it, but the only thing that isn't happening is the actual hiding/unhiding of the column/row. I've copied my code out to a brand new book and it works. So I copied it back into the original book but as a fresh, blank sheet and it still works. But it still doesn't work in the original, sizable sheet. However when I copied this into a simple macro it does work exactly as required, hiding the correct columns, but at the push of a button:
Sub HideThem()
        Columns("N:O").EntireColumn.Hidden = True '<= DOES work
End Sub
That said, I need this to update automatically based on the value of a single cell. I've even tried to call this mini Sub from within the Worksheet_Change() event but that didn't work either. Admittedly it's [I]very[/I] basic code, which is why it's infuriating that I can't get it to work! Are there any known conflicts with other commands/events, on-sheet buttons, images, merged cells etc that could be preventing the columns/rows from hiding? I can't for the life of me figure out why the rows/column won't hide automatically when they do either manually (Right-click Hide) or from my macro button press. I even tried to use a CheckBox instead of a YES/NO Data Validation cell to fire the code (as that could be acceptable) but when I try to insert an ActiveX CheckBox it says [I]Cannot insert object[/I], even in a brand new blank book. Could this be a related problem? Any thoughts on this would be much appreciated as I'm tearing my hair out!


This question generated 14 answers. To proceed to the answers, click here.

This thread is current as of June 09, 2015.


For more resources for Microsoft Excel