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

 

[srsanddrs]

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

the vba code name is Sheet6. it is visible and while it will be protected when this is resolved, it is not yet protected

 

[Joe4]

You are trying to refer to the sheet by name in your code, not by index. It looks like it is not named "Sheet6", but rather "Order Appendix - Parcel Only".

So you either need to refer to it like:

Worksheets("Order  Appendix - Parcel Only").Range("A32,A37").EntireRow.Hidden = True

or like:

Sheet6.Range("A32,A37").EntireRow.Hidden = True

And the same is true for the rest of the sheets in your code.

 

[srsanddrs]

You are trying to refer to the sheet by name in your code, not by index. It looks like it is not named "Sheet6", but rather "Order Appendix - Parcel Only".

So you either need to refer to it like:

Worksheets("Order  Appendix - Parcel Only").Range("A32,A37").EntireRow.Hidden = True

or like:

Sheet6.Range("A32,A37").EntireRow.Hidden = True

And the same is true for the rest of the sheets in your code.

Oh my gosh, feeling silly on that one. It fixed the issue. Thank you so very much!

 

[Joe4]

You are welcome!
Glad I was able to help.