EntireColumn.Hidden doesn't work in Worksheet_Change()

J

JollyRocker

New Member
Joined
Jun 9, 2015
Messages
6
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.

Code: 
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:

Code: 
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!
 
Sort by date Sort by votes
Hi,
try this:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)


    If Not Intersect(Target, Me.Range("$C$2")) Is Nothing Then
        
        Me.Columns("N:O").EntireColumn.Hidden = UCase(Target.Value) = "NO"
        Me.Range("$C$3").Value = IIf(UCase(Target.Value) = "NO", "Invisible", "Visible")
        
    End If
    
End Sub

Dave
 
Upvote 0
Thanks Dave, tried your solution and it's firing OK but for some reason still doesn't hide/unhide my columns in this historical sheet that I'm trying to reverse engineer and vastly improve! :confused:

Thanks anyway.


dmt32 said:
Hi,
try this:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)


    If Not Intersect(Target, Me.Range("$C$2")) Is Nothing Then
        
        Me.Columns("N:O").EntireColumn.Hidden = UCase(Target.Value) = "NO"
        Me.Range("$C$3").Value = IIf(UCase(Target.Value) = "NO", "Invisible", "Visible")
        
    End If
    
End Sub

Dave
Click to expand...
 
Upvote 0
No need of EntireColumn property when the object is (already) columns.

Code: 
Columns("N:O").Hidden = True
 
Upvote 0
JR,

Welcome to MrExcel.

On the face of it this is a strange one.
Therefore a silly question....

Is there any way that particular sheet has other code that reverses the WSC event code? Eg say Worksheet_Selection_Change code that effectively unhides the columns on every confirmation /move?


Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.EntireColumn.Hidden = False
End Sub

Maybe you can post link to an example of the offending file?
 
Last edited:
Upvote 0
Hi Tony,

Thanks for your reply. Before I started there was only one piece of VBA and that is a macro that only runs at the push of a button. That said, I've done a full search of the whole workbook just in case and this is the only instance of a hidden command.

What is curiouser, is that as a workaround I have now been playing about with Form CheckBoxes (since somehow I can't place an ActiveX Checkbox in any book).

On ticking it fires OK but still doesn't hide columns N:O, whether using EntireColumn or not. However I've now included another Column I within the same procedure and that does hide (both by referring to directly by letter and by named range) so there must be something about these particular columns that Excel doesn't like. But even Column I only hides when using the CheckBox, not from within the Worksheet_Change()! :confused:

Code: 
Sub ToggleWorkshare_Click()
   If ActiveSheet.Shapes("Check Box LCC").OLEFormat.Object.Value = 1 Then
      MsgBox "You ticked YES"
      ActiveSheet.Columns("I").Hidden = False     ' <= Does Work
      ActiveSheet.Columns("N:O").Hidden = False ' <= Does NOT Work
'      ActiveSheet.Columns("N").Hidden = False    ' <= Does NOT Work
'      ActiveSheet.Columns("O").Hidden = False    ' <= Does NOT Work
   Else
      MsgBox "You ticked NO"
      ActiveSheet.Columns("I").Hidden = True      ' <= Does Work
      ActiveSheet.Columns("N:O").Hidden = True  ' <= Does NOT Work
'      ActiveSheet.Columns("N").Hidden = True    ' <= Does NOT Work
'      ActiveSheet.Columns("O").Hidden = True    ' <= Does NOT Work
   End If
End Sub

I've even tried hiding columns N and O individually (by letter and named range) to see if one goes without the other, but nope. Column I goes, but N and O remain. There are no objects, or images in these columns and I've scanned down 4000 rows some way past the bottom of data to make sure there's no merged cells etc. that could be interfering, but alas nope.

Grrr!

Unfortunately I can't upload the workbook to the public domain as it contains confidential Corporate sensitive material.

I was hoping there may be something simple that I'm missing and you gurus have seen before, but I guess not. I'll keep plugging away and let you know if I get it working!


Regards,

Andy


Snakehips said:
JR,

Welcome to MrExcel.

On the face of it this is a strange one.
Therefore a silly question....

Is there any way that particular sheet has other code that reverses the WSC event code? Eg say Worksheet_Selection_Change code that effectively unhides the columns on every confirmation /move?


Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.EntireColumn.Hidden = False
End Sub

Maybe you can post link to an example of the offending file?
Click to expand...
 
Upvote 0
Can you remove all the data and just put the actual empty workbook somewhere?

The "Cannot insert object" error is to do with MS updates that broke activex controls. There are many articles about it out there, such as this (shameless self-promotion): Office Update breaks ActiveX controls | Excel Matters

It is possible that the problems are actually related.
 
Upvote 0
Correction on my last post:

On clicking the CheckBox, when specifying Columns individually, I and N both hide, O doesn't. So O must be my corrupt column, right?
However for some unfathomable reason N does not unhide, so there's obviously something awry with that too!

Hmmm.

I'll try fixing my ActiveX to see if that helps.

Andy.

JollyRocker said:
Hi Tony,

Thanks for your reply. Before I started there was only one piece of VBA and that is a macro that only runs at the push of a button. That said, I've done a full search of the whole workbook just in case and this is the only instance of a hidden command.

What is curiouser, is that as a workaround I have now been playing about with Form CheckBoxes (since somehow I can't place an ActiveX Checkbox in any book).

On ticking it fires OK but still doesn't hide columns N:O, whether using EntireColumn or not. However I've now included another Column I within the same procedure and that does hide (both by referring to directly by letter and by named range) so there must be something about these particular columns that Excel doesn't like. But even Column I only hides when using the CheckBox, not from within the Worksheet_Change()! :confused:

Code: 
Sub ToggleWorkshare_Click()
   If ActiveSheet.Shapes("Check Box LCC").OLEFormat.Object.Value = 1 Then
      MsgBox "You ticked YES"
      ActiveSheet.Columns("I").Hidden = False     ' <= Does Work
      ActiveSheet.Columns("N:O").Hidden = False ' <= Does NOT Work
'      ActiveSheet.Columns("N").Hidden = False    ' <= Does NOT Work
'      ActiveSheet.Columns("O").Hidden = False    ' <= Does NOT Work
   Else
      MsgBox "You ticked NO"
      ActiveSheet.Columns("I").Hidden = True      ' <= Does Work
      ActiveSheet.Columns("N:O").Hidden = True  ' <= Does NOT Work
'      ActiveSheet.Columns("N").Hidden = True    ' <= Does NOT Work
'      ActiveSheet.Columns("O").Hidden = True    ' <= Does NOT Work
   End If
End Sub

I've even tried hiding columns N and O individually (by letter and named range) to see if one goes without the other, but nope. Column I goes, but N and O remain. There are no objects, or images in these columns and I've scanned down 4000 rows some way past the bottom of data to make sure there's no merged cells etc. that could be interfering, but alas nope.

Grrr!

Unfortunately I can't upload the workbook to the public domain as it contains confidential Corporate sensitive material.

I was hoping there may be something simple that I'm missing and you gurus have seen before, but I guess not. I'll keep plugging away and let you know if I get it working!


Regards,

Andy
Click to expand...
 
Upvote 0
Having read post 6 then I would be pretty confident that Rory is correct in suggesting the problem is as a result of the Dec-14 updates.
I have experienced this and found the Fix to be quick and easy.

Good luck.
 
Upvote 0
Hi guys, yeah I cleaned out my *.exd files which has now let me place an ActiveX checkbox. So thank you.. however the columns still don't behave as desired!

Worksheet_Change() triggered by Data Validation "YES/NO" cell still has no effect on column visibility.

And applying the equivalent code to this ActiveX checkbox still has the same result of hiding only columns I and N, not O... and not unhiding N or O! (surely if it can be hidden, it can be unhidden??)

Rich (BB code): 
Private Sub CheckBox21_Click()   If CheckBox21 = True Then
      MsgBox "You just ticked ActiveX YES"
      ActiveSheet.Columns("I").Hidden = False ' <= Does Work
      ActiveSheet.Columns("N").Hidden = False ' <= Does NOTWork, Eh?
      ActiveSheet.Columns("O").Hidden = False ' <= Does NOT Work
   Else
      MsgBox "You just ticked ActiveX NO"
      ActiveSheet.Columns("I").Hidden = True ' <= Does Work
      ActiveSheet.Columns("N").Hidden = True ' <= DoesWork
      ActiveSheet.Columns("O").Hidden = True ' <= Does NOT Work
   End If
End Sub

And yes I realise the code can be rationalised, but I'll do that if/when(?) I actually get it to work in this step-by-step verbose fashion!


Regards,

Andy
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Dynamic Hide & Unhide column
Replies
3
Views
153
Kaper
Kaper
Y
  • Question Question
How to automatically hide/unhide columns based on related month with VBA
Replies
1
Views
456
ysfuyar
Y
J
  • Question Question
VBA Code works with worksheet_change but not as macro when called?
Replies
6
Views
80
jack109
J
R
  • Solved
Application.Intersect Range targeting Another Sheet's Cell Value
Replies
5
Views
55
Joe4
Joe4
V
  • Solved
Need VBA to split data into multiple sheets paste formulas, formatting AND data validation
Replies
2
Views
271
vanessavalentino83
V

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top