Hiding worksheets
in [Functions]
|
Prev: External Links in Excel 2007 with variable source file
Next: I can still select & manipulate locked & protected cells in '07
From: Gord Dibben on 12 Mar 2010 15:35 After hiding the sheets, protect the workbook under Tools>Protection. This will prevent unhiding sheets. Gord Dibben MS Excel MVP On Fri, 12 Mar 2010 11:10:10 -0800, Curtis <curtis.eadie(a)yahoo.ca> 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
From: Gord Dibben on 12 Mar 2010 15:36 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 <curtis.eadie(a)yahoo.ca> 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
From: Mike H on 12 Mar 2010 15:38
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 |