1

An excel spreadsheet that is as an input-form for users. Based on their input and selections via dropdown my intention is to guide them through the form by hiding & unhiding rows with input fields, presenting the users with the relevant questions.

On each row I have created an IF-formula that creates a 1 or 0 based on previous provided input 1 -> unhide the row , 0 -> hide the row.

So I'm looking for a macro that runs with every sheet calculation and hides or unhides the next rows as needed.

These formulas are in range I3:I70 on top of that I created a summary field in I2 =sum(I3:I70) so i thought I can either check changes in the range I3:I70 or changes on cell I2 to trigger the macro. [Neither solution fixed my problem]

I've tried several code examples discribed on the forums and I've tested the macros that checks for change in the range or the cell individually. As long as I call a test macro with a MsgBox it works fine. Also the macro that hides or unhides runs fine when I call it manually.

My problem: When I let the 'auto'-macro call the 'hide'-macro, Excel simply crashes; no warnings, nothing --> just crash.

My code:

Private Sub Worksheet_Calculate()
    Dim Xrg As Range
    Set Xrg = Range("H3:H70")
    If Not Intersect(Xrg, Range("H3:H70")) Is Nothing Then
    Macro1
    End If
End Sub

Sub Sample()
MsgBox "Yes"
End Sub

Sub Macro1()
    Dim cell As Range
    For Each cell In Range("H3:H70")
        If Not IsEmpty(cell) Then
            If cell.Value = 0 Then
                cell.EntireRow.Hidden = True
            End If
            If cell.Value = 1 Then
                cell.EntireRow.Hidden = False
            End If
        End If
    Next
End Sub

Thanks for any suggestions and tips in advance.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Radmila
  • 11
  • 1
  • 1
    I think [THIS](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure) is what you are looking for? – Siddharth Rout May 21 '21 at 11:38
  • What is the purpose of `If Not Intersect(Xrg, Range("H3:H70")) Is Nothing Then` when you explicitly define `Set Xrg = Range("H3:H70")`. Intersect is always going to return a range, the If statement is always true. You may as well just remove all of that and run Macro1 on every calculation. – Toddleson May 21 '21 at 13:57

0 Answers0