Hiding sheets based on user

Is there a way to create conditional workbook and worksheet views based on
the value of a particular cell?

Below is what I'm trying to accomplish:

I'm building a master budget template with multiple worksheets and not all
departments will use all worksheets. So, on the main page of the workbook I
want to have users select a department from a drop down menu and based on
that selection I only want worksheets that pertain to that particular
department to be displayed and worksheets that don't pertain to the
department hidden. Additionally I want show/hide certain columns in some of
the worksheets based on department selection on the main page.

Unfortunately, I do not know visual basic at all.

Thanks for any help.

James

You can hide sheets and/or columns, but Excel's security is not very
strong. There's information on J.E. McGimpsey's site:

http://www.mcgimpsey.com/excel/removepwords.html

Instead, you could provide each department with their own copy of the
workbook, containing only the data pertinent to them.

Then, consolidate the information in a master workbook, to which only
you have access.

James wrote:
Is there a way to create conditional workbook and worksheet views based on
the value of a particular cell?

Below is what I'm trying to accomplish:

I'm building a master budget template with multiple worksheets and not all
departments will use all worksheets. So, on the main page of the workbook I
want to have users select a department from a drop down menu and based on
that selection I only want worksheets that pertain to that particular
department to be displayed and worksheets that don't pertain to the
department hidden. Additionally I want show/hide certain columns in some of
the worksheets based on department selection on the main page.

Unfortunately, I do not know visual basic at all.

Thanks for any help.

James



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

i agree with debra, i used the code given on the site and it cracked the
file i had created.

if you still want to go ahead and do what you want, i was dabbling with
a similar situation myself a little while ago. here is what i have.

open a sample workbook with three sheets in it. hit Alt + F11, it will
open the VBA window. in the left panel, find ThisWorkbook, double click
on it and in the right window paste the following:

'from here
Options explicit

Private Sub Workbook_Open()

Sheet2.Visible = False
Sheet3.Visible = False

End Sub
'to here

now find Sheet1(Sheet1) in the left window and double click on it. in
the right window, paste the following:

'from here
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If LCase(Sheet1.Range("a1").Value) = LCase("Sheet2") Then

Sheets("Sheet2").Visible = True
Sheets("Sheet3").Visible = False

ElseIf LCase(Sheet1.Range("a1").Value) = LCase("Sheet3") Then

Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = True

End If
End Sub
'to here

now hit Alt + F11. you should go back to excel window. save the file.
close it and open it again. it should ask you to disable / enable
macros. select enable. the book should now open with sheet1 visible and
sheet2 and sheet3 invisible.

enter "Sheet2" in cell A1 of Sheet1 (without the quotes). you should
see Sheet2 becoming visible, while Sheet3 remains invisible.

enter "Sheet3" in cell A1 of Sheet1 (without the quotes). you should
see Sheet3 becoming visible, while Sheet3 becomes invisible.

try it and see if this is what you want.


---
Message posted from http://www.ExcelForum.com/

forgot to mention one thing. the solution is a very basic one. it does
not password-protect sheets. i am not an expert myself, but i could
probably supersize this code to password-protect. but then, the code in
the link provided by debra would be able to crack it.


---
Message posted from http://www.ExcelForum.com/