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.
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:
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
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!
Sub HideThem() Columns("N:O").EntireColumn.Hidden = True '<= DOES work End Sub
This question generated 14 answers. To proceed to the answers, click here.
This thread is current as of June 09, 2015.