EntireColumn.Hidden in Worksheet_Change()

Question

Using Excel 2010 I am editing an existing unprotected workbook and have created EntireColumn.Hidden and EntireRow.Hidden in 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 as I have MsgBoxes to prove it. I can change cell values etc., but not the hiding/unhiding of the column/row.

I've copied my code to a new book and it works. So I copied it back into the original book but as a fresh, blank sheet and it works. It still doesn't work in the original, sizable sheet.

When I copied this into a simple macro it does work 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

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.

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 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 Cannot insert object, even in a brand new blank book. Could this be a related problem?

Answer 1

I suppose you have a drop-down list in cell C3 with two items, viz "Visible" and "Invisible". The following code will hide Columns N and O when you change the value of Range C3 from blank / "Visible" to "Invisible". Prior to this action, you will have to read the message and click OK. Changing from "Invisible" to "Visible" will present you with a message box. Click OK and see the hidden columns reveal themselves.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("C3") = "Invisible" Then
        MsgBox ("You just changed to HIDE")
        Columns(14).Hidden = True
        Columns(15).Hidden = True
    Else
    If Range("C3") = "Visible" Then
        MsgBox ("You just changed to UNHIDE")
        Columns(14).Hidden = False
        Columns(15).Hidden = False
    End If
  End If
End Sub