EntireColumn.Hidden doesn't work in Worksheet_Change()

[JollyRocker]

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 very 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 Cannot insert object, 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!

 

[Snakehips]

Does the issue persist if in a copy of the sheet, you delete all data?

If so, what happens if you delete O & N which effectively creates a new O & N?

If so can you as Rory suggested provide a link to file containing that non-sensitive sheet?

 

[RoryA]

Any conditional formatting applied to those columns?

 

[JollyRocker]

Thanks Tony,

I copied my sheet within the same book, deleted N & O and inserted 2 new columns in their place... and they still don't hide.

HOWEVER... I copied my full sheet out in isolation into a new book, and even without modifying the columns, my Data Validation cell, ActiveX CheckBox and Form CheckBox (even when still linked to original macro Sub in existing book) all work exactly as I had intended!

So I'm guessing there must be a conflict with links to other sheets?

And to answer Rory's question, yes there is a LOT of conditional formatting on every row in most columns. However this has not been modified in my copied out sheet and as you can see above it works in isolation.


So, taking that one step further, I copied my Module across to the new book and hey presto my hiding commands break again, so there must be a conflicting line/procedure in that code!

And it turns out you are BOTH right. By process of elimination I have discovered the root cause:
Last week (before trying my hides) I had created a simple one-liner Function in my module as follows:

Function OverwrittenFormula(rng As Range) As Boolean
    OverwrittenFormula = Not rng.HasFormula
End Function

and called it from the conditional formatting to highlight any cell which has legitimately been manually fudged from the pre-determined formula result.

Even though none of the cells in N or O use this particular Conditional Formatting (it's purely isolated to Cols CJ:CO), when I remark out this one-liner out, even in my real book, all my hides work perfectly. That said, columns N and O formula refer to CL and CM values respectively so there's obviously some conflict here!

It deserves further investigation on my part in the morning, but at least I'm nearly there in less than a day of asking my first question, so I can sleep easy!

Thanks for the tips guys!


Regards,

Andy

Does the issue persist if in a copy of the sheet, you delete all data?

If so, what happens if you delete O & N which effectively creates a new O & N?

If so can you as Rory suggested provide a link to file containing that non-sensitive sheet?

 

[Snakehips]

Glad you are getting somewhere. If we helped nudge you in the right direction then that's great.
Good Luck.