Hide & unhide rows

[timesareGMT]

Hi all,

I create a calculation page on which the final result displays in cell D21.

How can I set up a macro to:
1. If D21 < 0.5, hide rows 22 to 28.
2. If D21 > 0.5, unhide rows 22 to 28.

And, how can I prevent the assigned macro from being stopped running when I password protect the spreadsheet?

Thanks a lot.

 

[Ingolf]

Hi,

Weaver, how can I change your code if I want in same sheet to hide columns E:O if cell A2 is > than a value. Not in same time with hide rows D22:D28
I change your code to do what I want but I it give me error: Ambiguos name Private Sub Worksheet_Change(ByVal Target As Range)
Thank you.

 

[timesareGMT]

This code should be placed in the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$21" Then
    Range("D22:D28").Select
    If Range("D21").Value < 0.5 Then
        Selection.EntireRow.Hidden = True
    Else
        Selection.EntireRow.Hidden = False
    End If
End If
End Sub

Are you password protecting the individual worksheet, or the entire workbook?

I protect the worksheet individually.

Thanks.

 

[dangre]

I protect the worksheet individually.

Thanks.

Just need a little more info. Do you have a formula in cell D21 or do you type your values directly into the cell?

 

[dangre]

Hi,

Weaver, how can I change your code if I want in same sheet to hide columns E:O if cell A2 is > than a value. Not in same time with hide rows D22:D28
I change your code to do what I want but I it give me error: Ambiguos name Private Sub Worksheet_Change(ByVal Target As Range)
Thank you.

You can change his code this way. Will hide the columns if A2 value is greater than zero.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo safeExit
    If Target.Address = "$A$2" Then
        Application.EnableEvents = False
        Range("E:O").EntireColumn.Hidden = (Target.Value > 0)
    End If
safeExit:
    Application.EnableEvents = True
End Sub

 

[Weaver]

The error may be caused by having more than one worksheet_change event on the same worksheet.

If you're testing for more than 1 condition, you're going to have to combine into one code. I can help you do this if you post all of what you have so far.

 

[timesareGMT]

You can change his code this way. Will hide the columns if A2 value is greater than zero.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo safeExit
    If Target.Address = "$A$2" Then
        Application.EnableEvents = False
        Range("E:O").EntireColumn.Hidden = (Target.Value > 0)
    End If
safeExit:
    Application.EnableEvents = True
End Sub

Thanks for all your info.

But I'm confronting a more serious problem:

I cannot get the macro run if I password protect the spreadsheet.

Since my file consists of couples of spreadsheets with different macro settings and I plan to pw protect them individually (using the same pw)

Will there be a solution??

 

[Weaver]

Apologies - I'd forgotten all about the password issue

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo safeExit
    ActiveSheet.Unprotect Password:="test"
    If Target.Address = "$A$2" Then
        
        Application.EnableEvents = False
        Range("E:O").EntireColumn.Hidden = (Target.Value > 0)
    End If
safeExit:
    ActiveSheet.Protect Password:="test"
    Application.EnableEvents = True
End Sub

You'd need to password protect your vb as well, otherwise people will be able to snoop your passwords.

 

[timesareGMT]

Apologies - I'd forgotten all about the password issue

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo safeExit
    ActiveSheet.Unprotect Password:="test"
    If Target.Address = "$A$2" Then
 
        Application.EnableEvents = False
        Range("E:O").EntireColumn.Hidden = (Target.Value > 0)
    End If
safeExit:
    ActiveSheet.Protect Password:="test"
    Application.EnableEvents = True
End Sub

You'd need to password protect your vb as well, otherwise people will be able to snoop your passwords.

Thank you so much.

 

[Ingolf]

The error may be caused by having more than one worksheet_change event on the same worksheet.

If you're testing for more than 1 condition, you're going to have to combine into one code. I can help you do this if you post all of what you have so far.

Hello, Weaver,

Yes, I need to combine into one code. Thank you.

 

[Ingolf]

Bump!