If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Hiding worksheets
Is there any way to keep users from un-hiding worksheets in a workbook?
However they do require access to other sheets (not hiiden) within the workbook Thanks -- ce |
#2
|
|||
|
|||
Hiding worksheets
Hi
Start VBA editor (Alt+F11). In VBA Project select sheet, and set it's Visible property to xlSheetVeryHidden. To prevent the user setting the sheets Visible property to xlSheetVisible again, protect VBA Project with password. Arvi Laanemets "Curtis" wrote in message ... Is there any way to keep users from un-hiding worksheets in a workbook? However they do require access to other sheets (not hiiden) within the workbook Thanks -- ce |
#3
|
|||
|
|||
Hiding worksheets
Hi Curtis
Right click on the sheet tabView CodeF4 to view the Properties windowsscroll down to Visibleselect xlSheetVeryHidden from the dropdown on the right. A Very Hidden sheet, does not show VBA and lock the project with a password to prevent them opening the VB Editor. -- Regards Roger Govier Curtis wrote: Is there any way to keep users from un-hiding worksheets in a workbook? However they do require access to other sheets (not hiiden) within the workbook Thanks |
#4
|
|||
|
|||
Hiding worksheets
Hi,
The short answer is you can't but you can make it more difficult. Alt+F11 to open VB editor and if the 'Project' window and 'properties' window aren't visible tap CTRL+R & F4 to show them In the project window select the sheets in turn that you want to hide and in the properties window set the 'visible' property to 'XlSheetVeryhidden' Now click Tools|VBA Project properties and on the 'protection' tab check 'Lock project for viewing' and apply a password. Save close and re-open. It's now as difficult as it gets and the only way to make the sheet visible is from the VBA editor but it isn't secure because that's the nature of Excel security -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Curtis" wrote: Is there any way to keep users from un-hiding worksheets in a workbook? However they do require access to other sheets (not hiiden) within the workbook Thanks -- ce |
#5
|
|||
|
|||
Hiding worksheets
Thanks I have another question if I may. I have 30 sheets in a particular workbook and each is password protected with certain cells within each sheet unlocked for user inputting. Is ther a way to lock/ unlock multiple sheets at a time (same password) Thanks ce -- ce "Mike H" wrote: Hi, The short answer is you can't but you can make it more difficult. Alt+F11 to open VB editor and if the 'Project' window and 'properties' window aren't visible tap CTRL+R & F4 to show them In the project window select the sheets in turn that you want to hide and in the properties window set the 'visible' property to 'XlSheetVeryhidden' Now click Tools|VBA Project properties and on the 'protection' tab check 'Lock project for viewing' and apply a password. Save close and re-open. It's now as difficult as it gets and the only way to make the sheet visible is from the VBA editor but it isn't secure because that's the nature of Excel security -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Curtis" wrote: Is there any way to keep users from un-hiding worksheets in a workbook? However they do require access to other sheets (not hiiden) within the workbook Thanks -- ce |
#6
|
|||
|
|||
Hiding worksheets
After hiding the sheets, protect the workbook under ToolsProtection.
This will prevent unhiding sheets. Gord Dibben MS Excel MVP On Fri, 12 Mar 2010 11:10:10 -0800, Curtis wrote: Is there any way to keep users from un-hiding worksheets in a workbook? However they do require access to other sheets (not hiiden) within the workbook Thanks |
#7
|
|||
|
|||
Hiding worksheets
Sub ProtectAllSheets()
Application.ScreenUpdating = False Dim N As Single For N = 1 To Sheets.Count Sheets(N).Protect Password:="justme" Next N Application.ScreenUpdating = True End Sub Sub UnprotectAllSheets() Application.ScreenUpdating = False Dim N As Single For N = 1 To Sheets.Count Sheets(N).Unprotect Password:="justme" Next N Application.ScreenUpdating = True End Sub Gord Dibben MS Excel MVP On Fri, 12 Mar 2010 12:11:01 -0800, Curtis wrote: Thanks I have another question if I may. I have 30 sheets in a particular workbook and each is password protected with certain cells within each sheet unlocked for user inputting. Is ther a way to lock/ unlock multiple sheets at a time (same password) Thanks ce |
#8
|
|||
|
|||
Hiding worksheets
Hi,
Is ther a way to lock/ unlock multiple sheets at a time (same password) Once again No. But you can loop through them and do it. Alt+f11 to open vbeditor. Right click 'ThisWorkbook' and insert module and paste the code below in. Change this line S = "Sheet1,Sheet2,Sheet3" to contain the names off all the sheet to unlock. Run the code and your done Sub Sonic() Dim ws As Worksheet Dim V As Variant Dim S As String S = "Sheet1,Sheet2,Sheet3" V = Split(S, ",") For Each ws In ThisWorkbook.Worksheets If Not IsError(Application.Match(ws.Name, V, 0)) Then ws.Unprotect Password:="MyPass" End If Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Curtis" wrote: Thanks I have another question if I may. I have 30 sheets in a particular workbook and each is password protected with certain cells within each sheet unlocked for user inputting. Is ther a way to lock/ unlock multiple sheets at a time (same password) Thanks ce -- ce "Mike H" wrote: Hi, The short answer is you can't but you can make it more difficult. Alt+F11 to open VB editor and if the 'Project' window and 'properties' window aren't visible tap CTRL+R & F4 to show them In the project window select the sheets in turn that you want to hide and in the properties window set the 'visible' property to 'XlSheetVeryhidden' Now click Tools|VBA Project properties and on the 'protection' tab check 'Lock project for viewing' and apply a password. Save close and re-open. It's now as difficult as it gets and the only way to make the sheet visible is from the VBA editor but it isn't secure because that's the nature of Excel security -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Curtis" wrote: Is there any way to keep users from un-hiding worksheets in a workbook? However they do require access to other sheets (not hiiden) within the workbook Thanks -- ce |
Thread Tools | |
Display Modes | |
|
|