Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding worksheets based on user permissions
Michael,
Go to the VB Editor and double click the ThisWorkbook module. You will find 2 drop down boxes at the top. From the left select Workbook. You should now find Private Sub Workbook_Open() *your code goes here* End Sub Write your code in here. Your code might look something like this (Make sure that the welcome worksheet is the first sheet) Dim pswrd as String, x as Long pswrd = InputBox("Enter password","Password") If pswrd = "mypassword" ' select your own password For x = 2 to ActiveWorkbook.Worksheets.Count Sheets(x).Visible = True Next End If Worksheets("Welcome").Visible = xlVeryHidden This will unhide all worksheets and hide the welcome sheet Now in the same module from the right drop down select BeforeClose You should now have Private Sub Workbook_BeforeClose(Cancel As Boolean) *your code goes here* End Sub This code might look somethink like this Dim x as Long Sheets(1).Visible = True For x = 2 to ActiveWorkbook.Worksheets.Count Sheets(x).Visible = xlVeryHidden Next The xlVeryHidden sets it so that the user can't unhide the sheet from the Format menu. Amend the code the to hide and to unhide specific sheets. just add variations on these 2 lines and replace the x with either the sheet index number or the sheet name (in quotes) Sheets(x).Visible = xlVeryHidden Sheets(x).Visible = True You can get real cleaver and use different passwords to unhide just certain sheets. Once done - select VBAProjectProperties from the VBE Tools menu and protect the project. Now your users can't see the code. steve "Michael M" wrote in message ... I am a bit confused. Here is the behavior I want. When the user opens a workbook, he/she is prompted for a password. If the password is given, all sheets are visible and editable. If the password is not given, workbook still opens, but some sheets are hidden and those that are visible are read-only. How would I accomplish this and if a macro is involved, how do I prevent someone from diabling the macro to subvert this check? TIA for the help. Michael -----Original Message----- Michael, One trend that has been suggested a few times is to have a single worksheet with instructions to the user(s). In a Workbook_Close event all sheets except this one are xlveryhidden. In the Workbook_Open event all sheets are made visible and the instruction worksheet is hidden. You can add password input here to restrict what happens. You can even set it up to show selected worksheets by password used. As a final step the VB project is password protected to prevent anyone from seeing the code. steve "Michael Monteiro" wrote in message ... Is it possible to hide a worksheet from a particular set of users? I was thinking about password protecting my workbook. If the password is supplied, the workbook opens in edit mode. If the password is not supplied, it opens in read-only mode, but a couple of worksheets are hidden from view and therefore not accessible. How can I do this? Furthermore, if I can do it, how do I prevent a user from subverting by disabling macros? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hiding Worksheets and Unhiding them easily for Novice User | Excel Discussion (Misc queries) | |||
Hiding worksheets based on user selection | Excel Worksheet Functions | |||
Help w/range-based permissions in Excel webpage | Excel Worksheet Functions | |||
Hiding/Exposing Worksheets based on a Number Entry | Excel Discussion (Misc queries) | |||
Hiding columns based on user/password | Excel Worksheet Functions |