Protect sheet but hide rows? in Excel General Questions    |   |  Help   Search For:     In:   Office   Excel     Excel General Questions     Excel New users     Excel Application Errors     Excel Charts     Excel Programming     Excel Setup     Excel Worksheet Functions              FTM 3/13/2007 11:34 AM PST   Question   Hello All, I have a large spreadsheet that is used as a tool by many people, the formula in the spreadsheet have to be protected to prevent them being overwritten or deleted by users however I would like the users to be able to hide or unhide rows/columns. When I use the protect worksheet function I don't seem to be able to allow hide/unhide. Any help very welcome! Thanks     Was this post helpful to you?         Reply |   Top           ~*Amanda*~ 3/13/2007 12:26 PM PST   If the hiding and unhiding is to allow people to just see information that pertains to them, the AutoFilter feature might work better for you. "FTM" wrote: > Hello All, > I have a large spreadsheet that is used as a tool by many people, the > formula in the spreadsheet have to be protected to prevent them being > overwritten or deleted by users however I would like the users to be able to > hide or unhide rows/columns. When I use the protect worksheet function I > don't seem to be able to allow hide/unhide. > Any help very welcome! > Thanks     Was this post helpful to you?         Reply |   Top           JLatham  3/13/2007 1:48 PM PST   Answer   What version of Excel are you using. In Excel 2003 there is an option to permit formatting Rows (and/or columns) which will permit the operations on a protected sheet. Not sure if 2002 (XP) had that option or not (I believe it did), but 2000 and earlier do not have it. If these are always the same rows to be hidden/revealed, then you could do it with two macros - either attached to a button on the sheet or not; one to unprotect the sheet, unhide the rows, protect it again; the other to unprotect, hide, protect again. But I"m betting that ~*Amanda*~ has the better idea. "FTM" wrote: > Hello All, > I have a large spreadsheet that is used as a tool by many people, the > formula in the spreadsheet have to be protected to prevent them being > overwritten or deleted by users however I would like the users to be able to > hide or unhide rows/columns. When I use the protect worksheet function I > don't seem to be able to allow hide/unhide. > Any help very welcome! > Thanks     Was this post helpful to you?         Reply |   Top           FTM 3/14/2007 3:32 PM PST   Thank you this option to allow formatting of rows/columns works perfectly! (Auto filter is not applicable as the ablity to hide rows/columns is to allow users to personalise their view rather than filter information.) "JLatham" wrote: > What version of Excel are you using. In Excel 2003 there is an option to > permit formatting Rows (and/or columns) which will permit the operations on a > protected sheet. Not sure if 2002 (XP) had that option or not (I believe it > did), but 2000 and earlier do not have it. > If these are always the same rows to be hidden/revealed, then you could do > it with two macros - either attached to a button on the sheet or not; one to > unprotect the sheet, unhide the rows, protect it again; the other to > unprotect, hide, protect again. > > But I"m betting that ~*Amanda*~ has the better idea. > > "FTM" wrote: > > > Hello All, > > I have a large spreadsheet that is used as a tool by many people, the > > formula in the spreadsheet have to be protected to prevent them being > > overwritten or deleted by users however I would like the users to be able to > > hide or unhide rows/columns. When I use the protect worksheet function I > > don't seem to be able to allow hide/unhide. > > Any help very welcome! > > Thanks     Was this post helpful to you?         Reply |   Top           ianb 3/27/2008 1:59 AM PST   Quick question on this (sorry to hijack the discussion!). will this unprotecting and then protecting again work if you have a password used? obviously you don't want this to become common knowledge which I guess could be the case if someone goes into the macro itself. "JLatham" wrote: > What version of Excel are you using. In Excel 2003 there is an option to > permit formatting Rows (and/or columns) which will permit the operations on a > protected sheet. Not sure if 2002 (XP) had that option or not (I believe it > did), but 2000 and earlier do not have it. > If these are always the same rows to be hidden/revealed, then you could do > it with two macros - either attached to a button on the sheet or not; one to > unprotect the sheet, unhide the rows, protect it again; the other to > unprotect, hide, protect again. > > But I"m betting that ~*Amanda*~ has the better idea. > > "FTM" wrote: > > > Hello All, > > I have a large spreadsheet that is used as a tool by many people, the > > formula in the spreadsheet have to be protected to prevent them being > > overwritten or deleted by users however I would like the users to be able to > > hide or unhide rows/columns. When I use the protect worksheet function I > > don't seem to be able to allow hide/unhide. > > Any help very welcome! > > Thanks     Was this post helpful to you?         Reply |   Top           Gord Dibben 3/27/2008 2:53 PM PST   ActiveSheet.Unprotect Password:="justme" do the things you do ActiveSheet.Protect Password:="justme" If you lock the priject for viewing, users cannot see the code ir the password. Open VBE and right-click on your project/workbook and select Properties>Protection>Lock project for viewing. Give it a password then Save the workbook, close and re-open to see protection is on that project. Gord Dibben MS Excel MVP On Thu, 27 Mar 2008 02:00:01 -0700, ianb <ianb@discussions.microsoft.com> wrote: >Quick question on this (sorry to hijack the discussion!). > >will this unprotecting and then protecting again work if you have a password >used? obviously you don't want this to become common knowledge which I guess >could be the case if someone goes into the macro itself. > >"JLatham" wrote: > >> What version of Excel are you using. In Excel 2003 there is an option to >> permit formatting Rows (and/or columns) which will permit the operations on a >> protected sheet. Not sure if 2002 (XP) had that option or not (I believe it >> did), but 2000 and earlier do not have it. >> If these are always the same rows to be hidden/revealed, then you could do >> it with two macros - either attached to a button on the sheet or not; one to >> unprotect the sheet, unhide the rows, protect it again; the other to >> unprotect, hide, protect again. >> >> But I"m betting that ~*Amanda*~ has the better idea. >> >> "FTM" wrote: >> >> > Hello All, >> > I have a large spreadsheet that is used as a tool by many people, the >> > formula in the spreadsheet have to be protected to prevent them being >> > overwritten or deleted by users however I would like the users to be able to >> > hide or unhide rows/columns. When I use the protect worksheet function I >> > don't seem to be able to allow hide/unhide. >> > Any help very welcome! >> > Thanks     Was this post helpful to you?         Reply |   Top           ianb 3/29/2008 3:42 AM PST   Perfect, thanks for your help! Ian "Gord Dibben" wrote: > ActiveSheet.Unprotect Password:="justme" > > do the things you do > > ActiveSheet.Protect Password:="justme" > > If you lock the priject for viewing, users cannot see the code ir the password. > > Open VBE and right-click on your project/workbook and select > Properties>Protection>Lock project for viewing. > > Give it a password then Save the workbook, close and re-open to see protection > is on that project. > > > Gord Dibben MS Excel MVP > > On Thu, 27 Mar 2008 02:00:01 -0700, ianb <ianb@discussions.microsoft.com> wrote: > > >Quick question on this (sorry to hijack the discussion!). > > > >will this unprotecting and then protecting again work if you have a password > >used? obviously you don't want this to become common knowledge which I guess > >could be the case if someone goes into the macro itself. > > > >"JLatham" wrote: > > > >> What version of Excel are you using. In Excel 2003 there is an option to > >> permit formatting Rows (and/or columns) which will permit the operations on a > >> protected sheet. Not sure if 2002 (XP) had that option or not (I believe it > >> did), but 2000 and earlier do not have it. > >> If these are always the same rows to be hidden/revealed, then you could do > >> it with two macros - either attached to a button on the sheet or not; one to > >> unprotect the sheet, unhide the rows, protect it again; the other to > >> unprotect, hide, protect again. > >> > >> But I"m betting that ~*Amanda*~ has the better idea. > >> > >> "FTM" wrote: > >> > >> > Hello All, > >> > I have a large spreadsheet that is used as a tool by many people, the > >> > formula in the spreadsheet have to be protected to prevent them being > >> > overwritten or deleted by users however I would like the users to be able to > >> > hide or unhide rows/columns. When I use the protect worksheet function I > >> > don't seem to be able to allow hide/unhide. > >> > Any help very welcome! > >> > Thanks > >     Was this post helpful to you?         Reply |   Top           Gord Dibben 3/29/2008 9:27 AM PST   Glad to help. Gord On Sat, 29 Mar 2008 03:43:00 -0700, ianb <ianb@discussions.microsoft.com> wrote: >Perfect, thanks for your help! > >Ian > >"Gord Dibben" wrote: > >> ActiveSheet.Unprotect Password:="justme" >> >> do the things you do >> >> ActiveSheet.Protect Password:="justme" >> >> If you lock the priject for viewing, users cannot see the code ir the password. >> >> Open VBE and right-click on your project/workbook and select >> Properties>Protection>Lock project for viewing. >> >> Give it a password then Save the workbook, close and re-open to see protection >> is on that project. >> >> >> Gord Dibben MS Excel MVP >> >> On Thu, 27 Mar 2008 02:00:01 -0700, ianb <ianb@discussions.microsoft.com> wrote: >> >> >Quick question on this (sorry to hijack the discussion!). >> > >> >will this unprotecting and then protecting again work if you have a password >> >used? obviously you don't want this to become common knowledge which I guess >> >could be the case if someone goes into the macro itself. >> > >> >"JLatham" wrote: >> > >> >> What version of Excel are you using. In Excel 2003 there is an option to >> >> permit formatting Rows (and/or columns) which will permit the operations on a >> >> protected sheet. Not sure if 2002 (XP) had that option or not (I believe it >> >> did), but 2000 and earlier do not have it. >> >> If these are always the same rows to be hidden/revealed, then you could do >> >> it with two macros - either attached to a button on the sheet or not; one to >> >> unprotect the sheet, unhide the rows, protect it again; the other to >> >> unprotect, hide, protect again. >> >> >> >> But I"m betting that ~*Amanda*~ has the better idea. >> >> >> >> "FTM" wrote: >> >> >> >> > Hello All, >> >> > I have a large spreadsheet that is used as a tool by many people, the >> >> > formula in the spreadsheet have to be protected to prevent them being >> >> > overwritten or deleted by users however I would like the users to be able to >> >> > hide or unhide rows/columns. When I use the protect worksheet function I >> >> > don't seem to be able to allow hide/unhide. >> >> > Any help very welcome! >> >> > Thanks >> >>     Was this post helpful to you?         Reply |   Top         Return to Microsoft Communities   Notify me of replies