Hiding/Unhiding rows depending on cell selection

[VBNewbiwe83]

Hi,

I am trying to hide rows 78 to 84 if cell C75 is populated with 'Hold', 'Insolvency' or 'other' and show if any other option is selected 'Plan' or 'SST' but it is hiding only row 79 and 80. Can anybody help?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "C75" Then
    ActiveSheet.Unprotect "Experience"
    Select Case Target
        Case "Hold", "Insolvency", "Other": ActiveSheet.Rows("78, 79, 80, 81, 82, 83, 84").EntireRow.Hidden = True
    ActiveSheet.Protect "Experience", True, True
        End Select
        End If
End Sub

Thanks

 

[Rick Rothstein]

Give this Change event code a try...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "C75" Then
    Rows("78:84").Hidden = [OR(C75={"Hold","Insolvency"})]
  End If
End Sub

 

[VBNewbiwe83]

Hi,

Thanks for replying, I am getting error Run-time error '1004 Unable to set the hidden property of range class.

Is this due to password protection?

Thanks

 

[Fluff]

Yes it is, try

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "C75" Then
    Me.Unprotect "Password"
    Rows("78:84").Hidden = [OR(C75={"Hold","Insolvency"})]
    Me.Protect "Password"
  End If
End Sub

Change Password to suit

 

[VBNewbiwe83]

Thanks, the only problem I have now is it hides row 78, 79 and 80 but not 81 and 84.

I have some code that autofits these rows based on a vlookup selections. Could this be causing a problem?

Private Sub Worksheet_Calculate()
    Dim sAddress As String
    On Error GoTo errHandle
    ActiveSheet.Unprotect "Experience"
    sAddress = "A19, A21, A25, A28, A34, A36, A38, A44, A47, A49, A55, A58, A60, A62, A64, A70, A77, A81, A84, A92"
    'disable change events
    Application.EnableEvents = False
    Range(sAddress).EntireRow.AutoFit
    're-enable events
    Application.EnableEvents = True
    ActiveSheet.Protect "Experience", True, True
errHandle:
'if there is an error ensure to re-enable events
Application.EnableEvents = True
End Sub

thanks

 

[Fluff]

Could this be causing a problem?

Yes it is. Whilst the code Rick supplied will hide those rows, as soon as the sheet recalculates, rows 81 & 84 will become visible again.

 

[VBNewbiwe83]

No problem, thanks for your help