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

 

[Snakehips]

@povictory If your specified sheets exist then that code should be ok.

However, why the two different methods used to refer to the sheets?
Does Sheet1 actually exist or might you have deleted it?
NB Sheet1 is not the same as Sheets(1) which would refer to the first sheet in your array of tabs.
Do you have a typo sheet name for "Sheet9" ?

 

[Siddharth Rout]

You can use the Worksheet_Change event. Paste this code in the Sheet Code module of Sheet1.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa
   
    Application.EnableEvents = False
   
    If Not Intersect(Target, Range("H16")) Is Nothing Then _
    Worksheets("Sheet9").Visible = (Target = UCase("YES"))
   
Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

 

[My Aswer Is This]

The script works for me. Do you have a sheet named "Sheet9")

 

[povictory]

@povictory If your specified sheets exist then that code should be ok.

However, why the two different methods used to refer to the sheets?
Does Sheet1 actually exist or might you have deleted it?
NB Sheet1 is not the same as Sheets(1) which would refer to the first sheet in your array of tabs.
Do you have a typo sheet name for "Sheet9" ?

Ah...this is helpful. I sort of cobbled the code together by looking at issues that were similar but not quite the same and I didn't realize that I had two different methods in there...dumb move on my part!

So I fixed that and the code runs successfully now. However, I have to manually run it in order to make it work. It doesn't update upon inputting a value or deleting a value in cell H16. Any thoughts on how to have it run when there is input in that cell or the input is deleted?

Sub Tab_Prompts()

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

End Sub

 

[Siddharth Rout]

However, I have to manually run it in order to make it work. It doesn't update upon inputting a value or deleting a value in cell H16. Any thoughts on how to have it run when there is input in that cell or the input is deleted?

Seems like you missed my post?

 

[Snakehips]

On the basis that you will be in Sheet1 when changing Value in H16 then the below code in the Sheet1 code module.

Private Sub Worksheet_Change(ByVal Target As Range)
'Exit if not single cell
If Target.CountLarge > 1 Then Exit Sub
'Proceed if cell is H16
If Not Intersect(Target, Range("H16")) Is Nothing Then Sheet9.Visible = (Target = "Yes")
End Sub

 

[Siddharth Rout]

You can use the Worksheet_Change event. Paste this code in the Sheet Code module of Sheet1.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa
 
    Application.EnableEvents = False
 
    If Not Intersect(Target, Range("H16")) Is Nothing Then _
    Worksheets("Sheet9").Visible = (Target = UCase("YES"))
 
Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

A small typo in my post...

Target = UCase("YES")

Should be

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

 

[povictory]

Seems like you missed my post?

Sorry! I did miss your post. Unfortunately it doesn't seem to be working for me (even after correcting for the typo). Selecting "Yes" or "No" or clearing the cell doesn't hide/unhide the sheet9. I must still be doing something wrong.

When I enter Yes or No in the cell I get an Excel pop-up that says "Subscript out of range"

 

[povictory]

On the basis that you will be in Sheet1 when changing Value in H16 then the below code in the Sheet1 code module.

Private Sub Worksheet_Change(ByVal Target As Range)
'Exit if not single cell
If Target.CountLarge > 1 Then Exit Sub
'Proceed if cell is H16
If Not Intersect(Target, Range("H16")) Is Nothing Then Sheet9.Visible = (Target = "Yes")
End Sub

This is pretty close to what I'm trying to do! Sheet9 appears when selecting "Yes" and disappears when selecting "No". Only other functionality I'm looking for is to have sheet9 be invisible if H16 is empty. Also, if Yes is selected and sheet9 is visible, I'd like it to disappear when Yes is deleted out of H16. Any suggestions to add that?