Macro Hide Sheet2-11 based on Yes/No questions on Sheet1

[hschorsch]

Hi, still very new at macros and could use some assistance,

On the sheet "WorkBookSetUp" I am putting drop boxes in Column F starting with Row 3 and extending to row 13. Each will have option of Yes or No. I would like the other sheets in the work book to appear if yes is selected and disappear if no is selected. My first sheet to disappear/reappear is called "AcidCalculator".

Digging through the forums here and elsewhere I got the following macro, copied it into the code box for "workbooksetup" filled in the sheet name and the reference, hit run but I get a compile error "end if without block if" which i do not understand. Can someone please assist me in correcting my errors so I can complete this project.

Private Sub Worksheet_Change(ByVal Target As Range)
If "f3" = "yes" Then

Sheets("AcidCalculator").Visible = True​

Else​

Sheets("AcidCalculator").Visible = False​

End If​

End If
End Sub

 

[dmt32]

Hi,
you get the error because you have an extra Endif in your code:

If "f3" = "yes" ThenSheets("AcidCalculator").Visible = True
Else
Sheets("AcidCalculator").Visible = False
End If
End If

but you can do what you want with a single line:

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("AcidCalculator").Visible = Not Me.Range("f3").Value = "yes"
End Sub

Dave

 

[Snakehips]

Maybe look to deal with all 10 rows using.....

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("F3:F13")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Select Case Target.Row
Case 3
ShtName = "AcidCalculator"  'edit sheet namesbelow to suit the row number
Case 4
ShtName = "Second sheet"
Case 5
ShtName = "Third sheet"
Case 6
ShtName = "etc etc"
Case 7
ShtName = "etc etc"
Case 8
ShtName = "etc etc"
Case 9
ShtName = "etc etc"
Case 10
ShtName = "etc etc"
Case 11
ShtName = "etc etc"
Case 12
ShtName = "etc etc"
Case 13
ShtName = "etc etc"


End Select
On Error Resume Next
Sheets(ShtName).Visible = LCase(Target) = "yes"
On Error GoTo 0
End Sub

Hope that helps.

 

[hschorsch]

Thank you dmt32 your single line code was perfect for my needs.

Honestly I didn't test your version Snakehips since the other solution worked so easily for me but thank you for replying too.