Worksheet_Change(ByVal Target As Range) - Two Condition

[bsquad]

I have dropdowns in Cells(44,1) and Cells(44,6) with simple values of Yes or No
I am attempting to do a ByVal but I need allow the user to select in any order - meaning changing the value of Cells(44,6) first and Cells(44,1) second and vice versa.

I have tried AND in my statements as well and nothing seems to be working - I am pretty open at this point.
I am also going to need to put 7 more rows of these - but I am not sure if that would make a difference - the conditions are row based so (45,1) and (45,6) have no association to row 44's

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Cells(44, 1).Address Then
        If Target.Value = "No" Then
            If Cells(44, 6).Value = "No" Then
                ThisWorkbook.Worksheets(8).Visible = False
            Else
                ThisWorkbook.Worksheets(8).Visible = True
            End If
        Else
            ThisWorkbook.Worksheets(8).Visible = True
        End If
    End If
End Sub

 

[NARAYANK991]

Hi ,

See if this works.

Private Sub Worksheet_Change(ByVal Target As Range)
            With Target
                 If .Column <> 1 And .Column <> 6 Then Exit Sub
                 If .Row < 44 Or .Row > 50 Then Exit Sub
                 If ((Cells(.Row, 1) = vbNullString) Or (Cells(.Row, 6) = vbNullString)) Then Exit Sub
                 
                 If ((.Column = 1 And .Value = "Yes") Or (.Column = 6 And .Value = "Yes")) Then
                    ThisWorkbook.Worksheets(8).Visible = True
                 Else
                    ThisWorkbook.Worksheets(8).Visible = False
                 End If
            End With
End Sub

 

[bsquad]

Thank you for the reply,
I was able to figure it out. the one you had posted was still hiding the tab if either Col 1 or Col 6 was = No
no worries though, appreciate the post

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A44")) Is Nothing Then
        If Target.Value = "No" And Range("F44").Value = "No" Then
            ThisWorkbook.Worksheets(8).Visible = False
        Else
            ThisWorkbook.Worksheets(8).Visible = True
        End If
    End If
End Sub

 

[mikerickson]

How about

Private Sub Worksheet_Change(ByVal Target As Range)

     ThisWorkbook.Worksheets(8).Visible = IIF((Range("A44").Value="No") And (Range("F45").Value = "No")), xlSheetHidden, xlSheetVisible)

End If

 

[bsquad]

Mikerickson,

I am getting a syntax error, I tried removing a parenthesis off of the last 'No' and also tried adding an additional parenthesis to the first 'Range' to resolve the syntax, but no joy. I love the approach with this one though, I don't think I've ever seen it like this before. Let me know your thoughts on how to resolve - if you are still up to it.

 

[mikerickson]

Add a third left paren. after the IFF

IFF(((Range.....

 

[bsquad]

Mikerickson,

I did originally try that and it didn't work, but looking at it closer I saw the A44, and F45 when I needed F44, but this works amazing and like I mentioned haven't seen this approach before so I really appreciate it thank you.