PDA

View Full Version : Hiding worksheets based on one cell



JDizzle
02-28-2006, 09:50 AM
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.
:igiveup:

debauch
02-28-2006, 09:58 AM
You could have something like this :


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


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 :


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

JDizzle
02-28-2006, 11:59 AM
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
02-28-2006, 12:11 PM
Optional methods included.
Regards
MD


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

JDizzle
02-28-2006, 12:32 PM
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
02-28-2006, 01:15 PM
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
02-28-2006, 02:15 PM
Ah very good....it works perfectly thank you very much for the help!

mdmackillop
02-28-2006, 02:17 PM
Glad to help.
You can mark the thread Solved using Thread Tools at the head of the page.