VBA code to make tab visible depending on input in another sheet

[povictory]

Hello - I've done a lot of searching on various forums but I'm unable to figure out why my code isn't working. I am trying to have a tab show up as visible in the workbook if "Yes" is selected in another cell. If "No" or nothing is selected in the dropdown, I want the tab to remain hidden. I keep getting run-time error 9 and I'm not sure what I need to change to get it to work. Any assistance is greatly appreciated. Thanks!

Sub Tab_Prompts()

If Sheet1.Range("H16") = "Yes" Then
    Worksheets("Sheet9").Visible = True
  Else
    Worksheets("Sheet9").Visible = False
        
  End If

End Sub

 

[povictory]

What do you mean by "when I delete data out of the cell". If you mean pressing the "DELETE" button when the cell is active, then the code will work as expected.

When I select the cell and press the "delete" button to clear out "Yes" or "No", I get the pop-up below and sheet9 still remains visible. I'd like it to be invisible when the cell is empty and when data is deleted from the cell.

 

[Siddharth Rout]

When I select the cell and press the "delete" button to clear out "Yes" or "No", I get the pop-up below and sheet9 still remains visible. I'd like it to be invisible when the cell is empty and when data is deleted from the cell.

It works just fine for me. By any chance is H16 a part of merged cell? If yes, then use

(UCase(Trim(Range("H16").Value2)) = "YES")

instead of

(UCase(Trim(Target.Value2)) = "YES")

 

[povictory]

It works just fine for me. By any chance is H16 a part of merged cell? If yes, then use

(UCase(Trim(Range("H16").Value2)) = "YES")

instead of

(UCase(Trim(Target.Value2)) = "YES")

Ugh...that was it. It was a merged cell of H16:H17. I'm so sorry about not making that clear. It works perfectly and you've solved my issue. Thank you so much for your help on this and especially your patience with me! It is much appreciated.

 

[povictory]

One other question - I want to add another line of code that will cause a tab (sheet9 in the example below) to appear if *anything* is typed into a cell and disappear if the cell is empty or has no data in it. So a similar idea to what the previous request but I want the tab to show up on any kind of input instead of just selecting "Yes". How would I adjust the code below to do that?

If Not Intersect(Target, Range("H16")) Is Nothing Then _
    Sheet9.Visible = (UCase(Trim(Range("H16").Value2)) = "YES")

 

[povictory]

One other question - I want to add another line of code that will cause a tab (sheet9 in the example below) to appear if *anything* is typed into a cell and disappear if the cell is empty or has no data in it. So a similar idea to what the previous request but I want the tab to show up on any kind of input instead of just selecting "Yes". How would I adjust the code below to do that?

If Not Intersect(Target, Range("H16")) Is Nothing Then _
    Sheet9.Visible = (UCase(Trim(Range("H16").Value2)) = "YES")

As an update, I got this partially working. The code that I'm using below works if I type in integers, but not text. I'm trying to do some researching and trial and error to get it to work with both but am not really clear to this point why it wouldn't work for a text string. Any advice on what I'm missing?

If Not Intersect(Target, Range("K9")) Is Nothing Then _
    Sheet7.Visible = (UCase(Trim(Range("K9").Value2)) = True)

 

[Siddharth Rout]

One other question - I want to add another line of code that will cause a tab (sheet9 in the example below) to appear if *anything* is typed into a cell and disappear if the cell is empty or has no data in it. So a similar idea to what the previous request but I want the tab to show up on any kind of input instead of just selecting "Yes". How would I adjust the code below to do that?

If Not Intersect(Target, Range("H16")) Is Nothing Then _
    Sheet9.Visible = (UCase(Trim(Range("H16").Value2)) = "YES")

Sheet9.Visible = (Len(Trim(Range("H16").Value2)) <> 0)

 

[povictory]

Sheet9.Visible = (Len(Trim(Range("H16").Value2)) <> 0)

Works perfectly...thank you!