Hoping for help - vba on worksheet 2 events

[srsanddrs]

Hello,

Would greatly appreciate some help on this issue. I have a multi worksheet book for quoting. There are a number of conditional elements that with some trial and error are all working except one thing. I have two change type events that need to trigger on the same worksheet, one changes elements within the worksheet and the other is meant to hide rows on other worksheets based on a condition in this worksheet. After learning I could not have two same event types on the same worksheet, i have tried combining based on what I could research. The code is just below. The part that is listed as the second block works fine. the top part does not. it gives no error message that i can see but nor am I able to step through the code in the vba module. Originally I had tried saying <> "Netsuite" and having the hide True come first but as that wasn't doing anything, tried this other way but same result. I had also originally tried using Row instead of Range but since not contiguous thought needed to be range.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$H$3" Then
        If Target.Value = "Netsuite" Then
            Worksheets("Sheet6").Range("A32,A37").EntireRow.Hidden = False
            Worksheets("Sheet7").Range("A35,A40,A44").EntireRow.Hidden = False
            Worksheets("Sheet8").Range("A38,A45,A48").EntireRow.Hidden = False
            Worksheets("Sheet9").Range("A44,A50,A53,A54").EntireRow.Hidden = False
            Worksheets("Sheet10").Range("A7,A52:54").EntireRow.Hidden = False
        Else
            Worksheets("Sheet6").Range("A32,A37").EntireRow.Hidden = True
            Worksheets("Sheet7").Range("A35,A40,A44").EntireRow.Hidden = True
            Worksheets("Sheet8").Range("A38,A45,A48").EntireRow.Hidden = True
            Worksheets("Sheet9").Range("A44,A50,A53,A54").EntireRow.Hidden = True
            Worksheets("Sheet10").Range("A7,A52:54").EntireRow.Hidden = True
        End If
    End If
    
' *** Next block*** [this part below seems to work just fine]
    
    If Target.Address = "$B$11" Then
        Range("b13:b22,b25:b31,B34:b38,j25:j31,j34:j38,k11,k13:k22,k25:k31,k34:k38").Value = ""
    End If
    
End Sub

 

[Joe4]

Welcome to the Board!

How exactly are you trying to trigger the first block?
If it is currently in the Sheet module, the first block of code will only run as cell H3 is being changed to "Netsuite".
Is that how you want it to work?

 

[srsanddrs]

Welcome to the Board!

How exactly are you trying to trigger the first block?
If it is currently in the Sheet module, the first block of code will only run as cell H3 is being changed to "Netsuite".
Is that how you want it to work?

Hi Thanks for reaching out.

I was trying to get it to hide the rows on the other sheets when H3's dropdown selection is anything other than Netsuite (or blank would be good to). When Netsuite is selected no rows in those other sheets should be hidden

 

[Joe4]

Hi Thanks for reaching out.

I was trying to get it to hide the rows on the other sheets when H3's dropdown selection is anything other than Netsuite (or blank would be good to). When Netsuite is selected no rows in those other sheets should be hidden

Right, but understand that the Worksheet_Change event procedure code only runs when the specific cell is changed.
So the way you have the code written, it will only run when cell H3 is physically changed.

 

[Joe4]

Also note that you have a few typos in your code.
These lines:

 Worksheets("Sheet10").Range("A7,A52:54")

should look like this:

 Worksheets("Sheet10").Range("A7,A52:A54")

 

[srsanddrs]

Right, but understand that the Worksheet_Change event procedure code only runs when the specific cell is changed.
So the way you have the code written, it will only run when cell H3 is physically changed.

Correct when that is changed, which happens anytime we quote it should hide, unhide the rows in those other sheets. It does not. Thanks for the typo catch! I fixed my typo and now i get this error when i change the value

 

[Joe4]

Correct when that is changed, which happens anytime we quote it should hide, unhide the rows in those other sheets. It does not. Thanks for the typo catch! I fixed my typo and now i get this error when i change the value

View attachment 66597

If you hit debug, which line of code does it highlight?
And what value exactly are you entering in to what cell that causes this error?

 

[srsanddrs]

If you hit debug, which line of code does it highlight?
And what value exactly are you entering in to what cell that causes this error?

I entered IFS (another ERP) from the drop down so the expectation was rows would hide in those other sheets

 

[srsanddrs]

View attachment 66598

I entered IFS (another ERP) from the drop down so the expectation was rows would hide in those other sheets

should clarify - entered into the drop down in h3

 

[Joe4]

Do you have a Sheet named exactly "Sheet6"?
Is it visible?
Is it protected?