PDA

View Full Version : [SOLVED] Hidden Object Range Failure in Worksheet Calculate



ravi1129
03-02-2018, 12:37 PM
I have a formula in Sheet2 named "Parameters" in cell D69.
This formula is pulling info from sheet 3 & sheet 4.

My excel keeps crashing with a Run-time error: Method 'Hidden' of object 'Range' failed.
I don't see how it could be in a loop?



In my sheet 2 - here is my vba code:

Option Explicit
Public OldValuetoTrackChange1 As Variant


Private Sub Worksheet_Calculate()
Dim CellNAErrorPresent As Boolean
CellNAErrorPresent = IsError(Range("D69").Value)
' If there is a #N/A error then don't execute below and set this temp variable to 0
If CellNAErrorPresent = False Then
If Range("D69").Value <> OldValuetoTrackChange1 Then
OldValuetoTrackChange1 = Range("D69").Value
End If
Else
OldValuetoTrackChange1 = 0
End If

' if my cell D69 is = "None" and not #N/A from the formula then hide the next two rows
If OldValuetoTrackChange1 = "None" Then
Rows("70:71").EntireRow.Hidden = True
Else
Rows("70:71").EntireRow.Hidden = False
End If
End Sub


In my ThisWorkbook - here is my code:

Private Sub Workbook_Open()
OldValuetoTrackChange1 = Sheets("Parameters").Range("D69").Value
End Sub




The Rows (70&71) I am trying to Hide have formulas in them that are pulling info from sheet 3 & 4 as well. Is that causing the issue? How do I avoid crashing in this scenario?

ravi1129
03-02-2018, 03:48 PM
Needed to add
Application.EnableEvents = True at the beginning after setting variables
and
Application.EnableEvents = False at the end before End Sub