Auto hide columns and rows based on cell values. Protect Sheet.

[mcarterlgk]

I have a sheet that needs to have columns hidden if the value in row 35 of the column is <0 and unhide the columns if isn't(The values in row 35 are calculated based on B8)

If B8<2 then rows 64-97 need to be hidden as well and unhiden if not.

I had code that worked at one point, but something changed and the code no longer automatically triggers the changes.

The code stopped working after I protected the sheet and no longer worked after I unprotected it. I do not know if this is the issue.

I would like the code to automatically unprotect and reprotect the sheet after hiding/unhiding the rows and columns. (if it necessary to unprotect to hide/unhide. I though it was, but I could be incorrect.)

My code is a bit of a mess as I am not normally a programmer and it was cobbled together through snippets found on mrexcel and elsewhere as well as advice from a previous posting.

my code:

Private Sub Worksheet_Change(ByVal Target As Range)


    If Intersect(Target, Me.Range("B8")) Is Nothing Then Exit Sub
    Application.EnableEvents = False 'to prevent endless loop
    
    
    Dim col As Range
    Dim rng As Range
    Dim x As Long
    
    
    Set rng = ActiveSheet.UsedRange
    x = 35
    
    'Hide columns based on value in row 35
    For Each col In rng.Columns
    y = col.Column
    If Cells(x, y) < 0 And y > 2 Then col.EntireColumn.Hidden = True Else: col.EntireColumn.Hidden = False
    Next col
    
    
    'Hide rows if B8 is < 2
    Application.EnableEvents = True
    
    If Target.Address = "$B$8" Then
    If Range("B8") < 2 Then
    Rows("64:97").Hidden = True
    End If
    If Range("B8") > 1 Then
    Rows("64:97").Hidden = False
    End If
    End If
    
End Sub

 

[scott_n_phnx]

Just a quick look at your code, your line <b>Application.EnableEvents = False</b> turns off the EnableEvents, but you do not have anything to turn it back on after the code completes. What I would recommend is to create a new module, and use the code below. Additionally, just before the End Sub of your code, add <b>Application.EnableEvents = True</b>.

Sub EnableEventsMacro()
Application.EnableEvents = True
End Sub

**EDIT: My bad, I just saw the entry for enabling the events. I still think that you need to move that to the end. It may be that due to it current placement, it is not being re-enabled. You might still try my suggestion to see if that helps.

 

[mcarterlgk]

I moved Application.EnableEvents = True to the end of the code. No Difference. Still will not automatically run.

Created a new module and attempted to run as a macro, errored out over improper use of Me.