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

 

[Siddharth Rout]

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"

This means 2 things

1. The code is working as expected
2. You are passing the wrong sheet and hence you are getting the "Subscript out of range". This error means that "Sheet9" doesn't exist. Please recheck.

 

[My Aswer Is This]

I asked the same question in my earlier post and never got an answer:

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

 

[My Aswer Is This]

These two sheets are not the same:
One is named "Sheet9"
The other one is sheet9

Sub My_Sheet()
'Modified 3/20/2022  2:36:26 AM  EDT
Sheet9.Range("A1").Value = "Alpha"
Sheets("Sheet9").Range("A1").Value = "Alpha"
End Sub

 

[povictory]

These two sheets are not the same:
One is named "Sheet9"
The other one is sheet9

Sub My_Sheet()
'Modified 3/20/2022  2:36:26 AM  EDT
Sheet9.Range("A1").Value = "Alpha"
Sheets("Sheet9").Range("A1").Value = "Alpha"
End Sub

Yes, I see that now. I'm trying to refer to the object sheet9. There is not a sheet named "Sheet9". Apologies for the confusion.

 

[povictory]

This means 2 things

1. The code is working as expected
2. You are passing the wrong sheet and hence you are getting the "Subscript out of range". This error means that "Sheet9" doesn't exist. Please recheck.

I'm trying to refer to the object sheet9. You are correct in that there is not a tab named "Sheet9". Sorry about the confusion.

 

[Siddharth Rout]

I'm trying to refer to the object sheet9. You are correct in that there is not a tab named "Sheet9". Sorry about the confusion.

Can you do this for me? Open VBA Editor and then post a screenshot of the Project Explorer on the left side?

 

[povictory]

Can you do this for me? Open VBA Editor and then post a screenshot of the Project Explorer on the left side?

Sure...no problem:

 

[Siddharth Rout]

In my code in Post #3

Change

Worksheets("Sheet9").Visible

to

Sheet9.Visible

or to

Worksheets("h").Visible

 

[povictory]

In my code in Post #3

Change

Worksheets("Sheet9").Visible

to

Sheet9.Visible

or to

Worksheets("h").Visible

Thank you - this works great when selecting Yes or No. However, when I delete data out of the cell I get a "Type mismatch" popup and the tab doesn't disappear. Is there a tweak I can make to do that?

 

[Siddharth Rout]

Thank you - this works great when selecting Yes or No. However, when I delete data out of the cell I get a "Type mismatch" popup and the tab doesn't disappear. Is there a tweak I can make to do that?

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.