Hiding worksheets based on one cell

[JDizzle]

I have an Excel worksheet with a Yes / No dropdown box - If the box is changed to "No" I need 2 of the other sheets in the book to be grayed out and/or inactivated.

How would I accomplish this? I have used VB.Net alot but not much Excel VBA at all. Actually I am not even very sure how to reference cells from the VBA interface within Excel (the Help is not that helpful).

Any expert advice and opinions would surely be welcome.

[debauch]

You could have something like this :

[vba]
If sheets.sheet("sheetname").range(a2) = "No" then
sheetname.visible = false
else sheetname.visible = true
End if
[/vba]

I am not in excel as we speak, so the code may not be exact, but it would work, or point you in the righgt direction.

P.S - that code, will actually hide the sheet completey. I am not sure about , deactivating it. You could try , " sheetname.enabled = false " , never had a use for it , so I am not 100% on that one.

I am sure there is a better way to do this, but here is a basic way that would work also :

[vba]
If sheets.sheet("sheetname").range(a2) = "No" then
sheetname.visible = false
End if
If sheets.sheet("sheetname").range(a2) = "YES" then
sheetname.visible = True
End if

[/vba]

[JDizzle]

So what event would I enter that under for the particular sheet? I would like it to occur as soon as the drop down is changed from yes to no or vice versa.

[mdmackillop]

Optional methods included.
Regards
MD

[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$3" Then
If UCase(Target) = "NO" Then
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Protect
Sheets("Sheet5").Protect
Else
Sheets("Sheet2").Visible = True
Sheets("Sheet3").Visible = True
Sheets("Sheet4").Unprotect
Sheets("Sheet5").Unprotect
End If
End If
End Sub
[/vba]

[JDizzle]

Ok, I think I've almost got this....using mdmackillop's code above I am getting a subscript out of range error. I added several "sheets(sheet#).protect = true" statements to line it up with whats in my workbook, but did I miss something.

Thanks for all the help it is very much appreciated.

[mdmackillop]

You would get that error if the sheet does not exist. If you click on the debug option, the offending item should be highlighted.

[JDizzle]

Ah very good....it works perfectly thank you very much for the help!

[mdmackillop]

Glad to help.
You can mark the thread Solved using Thread Tools at the head of the page.