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?

what happens when you step though the code in the original worksheet? — Siddharth Rout, Jun 09 '15 at 08:15
Try `Columns("N:O").Hidden = True` ie, remove the `EntireColumn` part — kaybee99, Jun 09 '15 at 08:15
Works here, in the sheet you are trying to use it with are there any things like merged cells in them columns. Just for the purpose of testing try removing all formatting from N:O and try then — 99moorem, Jun 09 '15 at 08:20
As for the cannot insert objects see http://stackoverflow.com/questions/27411399/microsoft-excel-activex-controls-disabled — 99moorem, Jun 09 '15 at 08:21
hi, are you manually changing the `Range("$C$2")` value or is it linked to another formula or code. Why i am asking this, if you are changing the value of that cell from another worksheet you need to identify worksheet names before your column commands. — Dubison, Jun 09 '15 at 11:20
I will second @99moorem that this code works fine for me. I added all of your code (including `MsgBox` and the value change in the other cell) and it all runs. I half expected the `MsgBox` to steal focus and maybe mess with the event, but it had no effect. What happens if you strip it down to the bare minimum call to hide the column? That is, remove all the bits about `Application`, `MsgBox`, and setting values? — Byron Wall, Jun 09 '15 at 13:54

score 0 · Answer 1 · answered Jul 16 '15 at 11:36

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

EntireColumn.Hidden in Worksheet_Change()

1 Answers1