How can I fix this code, I keep receiving an infinite loop?

Question

When I run this code and select yes in the cell "bulk" I keep receiving "please enter the number of labor hours" over and over. Basically what my goal is to have a drop down list to show hidden rows. Then if yes is selected in another drop down list, then two additional box inputs show up

Private Sub worksheet_change(ByVal target As Range)
ActiveSheet.Activate
Rows("20:22").EntireRow.Hidden = True
Rows("23:26").EntireRow.Hidden = True
Rows("27:30").EntireRow.Hidden = True
Rows("51:56").EntireRow.Hidden = True
If Not Application.Intersect(Range("Change"), Range(target.Address)) Is Nothing Then
        Select Case target.Value
        Case Is = "Asset Transfer": Rows("20:22").EntireRow.Hidden = False
                            Rows("23:26").EntireRow.Hidden = True
                            Rows("27:30").EntireRow.Hidden = True
                            Rows("51:56").EntireRow.Hidden = True
        Case Is = "Fund Lineup": Rows("27:30").EntireRow.Hidden = False
                            Rows("20:22").EntireRow.Hidden = True
                            Rows("23:26").EntireRow.Hidden = True
                            Rows("51:56").EntireRow.Hidden = True
        Case Is = "Plan Merge": Rows("23:26").EntireRow.Hidden = False
                            Rows("20:22").EntireRow.Hidden = True
                            Rows("27:30").EntireRow.Hidden = True
                            Rows("51:56").EntireRow.Hidden = True
        Case Is = "Loans": Rows("51:56").EntireRow.Hidden = False
                            Rows("27:30").EntireRow.Hidden = True
                            Rows("20:22").EntireRow.Hidden = True
                            Rows("23:26").EntireRow.Hidden = True
                            Rows("28:31").EntireRow.Hidden = True
        End Select
End If
Set target = Range("bulk")
If target.Value = "Yes" Then
Dim QtyEntry As Integer
Dim Msg As String
Msg = "Please enter the number of labor hours'"
QtyEntry = InputBox(Msg)
ActiveSheet.Range("c60").Value = QtyEntry
Dim Entry As Integer
Dim Msg1 As String
Msg1 = "Enter percentage increase'"
Entry = InputBox(Msg1)
ActiveSheet.Range("d60").Value = Entry
End If
End Sub

You did not `Disable Events`. Please see the solution [on this question](https://stackoverflow.com/a/50868969/6706419) — urdearboy, Jul 17 '18 at 14:43
thank you for showing me how to place my code with the correct indention — Alex Urquidi, Jul 17 '18 at 14:48

urdearboy · Accepted Answer · 2018-07-17T15:31:15.430

Once your cell has been changed, you can disable events right away, and then re-enable them before exiting the sub.

Also, you start the sub by hiding columns so there is no need to hide them again in your Select Case. All you need to do here is un-hide the rows that you want to be visible.

Also #2, are you sure you don't want your 2nd if statement inside your first if statement? As is, any change will prompt your input boxes.

You can reduce your code to this, which makes your logic a little easier to follow. The main take away is to notice that nothing is done outside of the events being disabled.

Option Explicit

Private Sub worksheet_change(ByVal target As Range)

Application.EnableEvents = False
    Union(Rows("20:30"), Rows("51:56")).EntireRow.Hidden = True
    If Not Application.Intersect(Range("Change"), Range(target.Address)) Is Nothing Then
        Select Case target.Value
            Case "Asset Transfer"
                Rows("20:22").EntireRow.Hidden = False
            Case "Fund Lineup"
                Rows("27:30").EntireRow.Hidden = False
            Case "Plan Merge"
                Rows("23:26").EntireRow.Hidden = False
            Case "Loans"
                Rows("51:56").EntireRow.Hidden = False
        End Select
    'Do you want your second IF statement here?
    End If

    If Range(“bulk”) = "Yes" Then
        Range("C60") = InputBox("Please enter the number of labor hours'")
        Range("D60") = InputBox("Enter Percentage Increase'")
    End If
Application.EnableEvents = True

End Sub

You will likely need to add some validation/error handling for both of your input boxes. What happens if the user puts "one" for number of labor hours? I recommend looking into Application.InputBox so you can control for the input.

i have a data validation on the cell – Alex Urquidi Jul 17 '18 at 16:52 — Alex Urquidi, Jul 17 '18 at 16:52
so this worked then? orrrrr? – urdearboy Jul 17 '18 at 16:54 — urdearboy, Jul 17 '18 at 16:54
yes thank you so much! really appreciate it! – Alex Urquidi Jul 17 '18 at 17:45 — Alex Urquidi, Jul 17 '18 at 17:45

How can I fix this code, I keep receiving an infinite loop?

1 Answers1