Hi
I used the following code to hide rows based on a cell value:
Private Sub Worksheet_Calculate()
Dim MyResult As String
Application.EnableEvents = False
Rows("11:" & Worksheets("Vouchers").UsedRange.Rows.Count).EntireRow.Hidden = False
MyResult = Worksheets("Vouchers").Cells(11, 3).Value
Select Case MyResult
Case "", "None"
Rows("3:37").EntireRow.Hidden = True
Case Else
Rows("3:37").EntireRow.Hidden = False
End Select
MyResult2 = Worksheets("Vouchers").Cells(65, 3).Value
Select Case MyResult2
Case "", "None"
Rows("58:72").EntireRow.Hidden = True
Case Else
Rows("58:72").EntireRow.Hidden = False
End Select
Application.EnableEvents = True
End Sub
It worked fine when I tested it, but once I closed the workbook and reopened it it seemed to stop working. I thought it might have been because I had protected the worksheet so I unprotected it and re-entered the code but it is still not working. The workbook is a macro enabled workbook and all the cell references have been double checked. Any ideas?
I used the following code to hide rows based on a cell value:
Private Sub Worksheet_Calculate()
Dim MyResult As String
Application.EnableEvents = False
Rows("11:" & Worksheets("Vouchers").UsedRange.Rows.Count).EntireRow.Hidden = False
MyResult = Worksheets("Vouchers").Cells(11, 3).Value
Select Case MyResult
Case "", "None"
Rows("3:37").EntireRow.Hidden = True
Case Else
Rows("3:37").EntireRow.Hidden = False
End Select
MyResult2 = Worksheets("Vouchers").Cells(65, 3).Value
Select Case MyResult2
Case "", "None"
Rows("58:72").EntireRow.Hidden = True
Case Else
Rows("58:72").EntireRow.Hidden = False
End Select
Application.EnableEvents = True
End Sub
It worked fine when I tested it, but once I closed the workbook and reopened it it seemed to stop working. I thought it might have been because I had protected the worksheet so I unprotected it and re-entered the code but it is still not working. The workbook is a macro enabled workbook and all the cell references have been double checked. Any ideas?