Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hiding rows while sheet is protected
I have several spreadsheets that I put together for other people to enter data and report results. The sheets are made of about 25 sets with four rows in each set. Rows #3 and #4 are where data is entered. I have shaded them green to distinguish them from the rows that contain formulas. Rows #1 and #2 are formulas, row #1 is a percentage based on the numbers entered in rows 3 and 4, e.g. 75%. Row 2 is a fraction based on the numbers entered in rows 3 and 4, e.g. 3/4. When the sheet is used as a report the green rows are hidden and the report is printed. I set up two custom views called "green rows hidden" and "green rows seen" to quickly switch back and forth between the two views. These reports are being used by a variety of people with widely varying computer skill. Despite frequent instruction some users regularly over-write formulas. I tried protecting the sheets, leaving the data entry cells unlocked, but when the sheets are protected, the custom views don't work and the green rows can't be hidden. What I would like to know is: is there a way to protect the formula cells and still be able to hide cells using the custom view function? Thanks in advance! Mike -- burnsbyrne ------------------------------------------------------------------------ burnsbyrne's Profile: http://www.excelforum.com/member.php...o&userid=22153 View this thread: http://www.excelforum.com/showthread...hreadid=574142 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hiding rows while sheet is protected
Unprotect the sheet, hide greens, re-protect the sheet.
Something like this. Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.Unprotect Password:="justme" ActiveWorkbook.CustomViews("green rows hidden").Show ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ActiveWorkbook.CustomViews("green rows seen").Show ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub Right-click on the Excel Icon left of "File" on the menubar. Select "View Code" Copy/paste the BeforePrint code above into that module. Gord Dibben MS Excel MVP On Tue, 22 Aug 2006 08:13:18 -0400, burnsbyrne wrote: I have several spreadsheets that I put together for other people to enter data and report results. The sheets are made of about 25 sets with four rows in each set. Rows #3 and #4 are where data is entered. I have shaded them green to distinguish them from the rows that contain formulas. Rows #1 and #2 are formulas, row #1 is a percentage based on the numbers entered in rows 3 and 4, e.g. 75%. Row 2 is a fraction based on the numbers entered in rows 3 and 4, e.g. 3/4. When the sheet is used as a report the green rows are hidden and the report is printed. I set up two custom views called "green rows hidden" and "green rows seen" to quickly switch back and forth between the two views. These reports are being used by a variety of people with widely varying computer skill. Despite frequent instruction some users regularly over-write formulas. I tried protecting the sheets, leaving the data entry cells unlocked, but when the sheets are protected, the custom views don't work and the green rows can't be hidden. What I would like to know is: is there a way to protect the formula cells and still be able to hide cells using the custom view function? Thanks in advance! Mike |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hiding rows while sheet is protected
If you will use this code, please make amendments to it.
Remove the line........... ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Gord On Tue, 22 Aug 2006 09:11:33 -0700, Gord Dibben <gorddibbATshawDOTca wrote: Unprotect the sheet, hide greens, re-protect the sheet. Something like this. Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.Unprotect Password:="justme" ActiveWorkbook.CustomViews("green rows hidden").Show ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ActiveWorkbook.CustomViews("green rows seen").Show ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub Right-click on the Excel Icon left of "File" on the menubar. Select "View Code" Copy/paste the BeforePrint code above into that module. Gord Dibben MS Excel MVP On Tue, 22 Aug 2006 08:13:18 -0400, burnsbyrne wrote: I have several spreadsheets that I put together for other people to enter data and report results. The sheets are made of about 25 sets with four rows in each set. Rows #3 and #4 are where data is entered. I have shaded them green to distinguish them from the rows that contain formulas. Rows #1 and #2 are formulas, row #1 is a percentage based on the numbers entered in rows 3 and 4, e.g. 75%. Row 2 is a fraction based on the numbers entered in rows 3 and 4, e.g. 3/4. When the sheet is used as a report the green rows are hidden and the report is printed. I set up two custom views called "green rows hidden" and "green rows seen" to quickly switch back and forth between the two views. These reports are being used by a variety of people with widely varying computer skill. Despite frequent instruction some users regularly over-write formulas. I tried protecting the sheets, leaving the data entry cells unlocked, but when the sheets are protected, the custom views don't work and the green rows can't be hidden. What I would like to know is: is there a way to protect the formula cells and still be able to hide cells using the custom view function? Thanks in advance! Mike Gord Dibben MS Excel MVP |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hiding rows while sheet is protected
Gord, Thanks for your response. I neglected to mention in my question that I am a non-programmer. While I recognize what you wrote as computer code and I am very grateful for your efforts, I have no idea what to do with it. In addition, I need something that will work for other people who are even less computer literate than I am. I was hoping that there would be some option I could choose when protecting the sheet that would allow hiding and unhiding the rows. If you can explain what to do with the code without taking up too much of your time I'd appreciate it. Thank you again, Mike Gord Dibben Wrote: If you will use this code, please make amendments to it. Remove the line........... ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Gord On Tue, 22 Aug 2006 09:11:33 -0700, Gord Dibben <gorddibbATshawDOTca wrote: Unprotect the sheet, hide greens, re-protect the sheet. Something like this. Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.Unprotect Password:="justme" ActiveWorkbook.CustomViews("green rows hidden").Show ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ActiveWorkbook.CustomViews("green rows seen").Show ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub Right-click on the Excel Icon left of "File" on the menubar. Select "View Code" Copy/paste the BeforePrint code above into that module. Gord Dibben MS Excel MVP On Tue, 22 Aug 2006 08:13:18 -0400, burnsbyrne wrote: I have several spreadsheets that I put together for other people to enter data and report results. The sheets are made of about 25 sets with four rows in each set. Rows #3 and #4 are where data is entered. I have shaded them green to distinguish them from the rows that contain formulas. Rows #1 and #2 are formulas, row #1 is a percentage based on the numbers entered in rows 3 and 4, e.g. 75%. Row 2 is a fraction based on the numbers entered in rows 3 and 4, e.g. 3/4. When the sheet is used as a report the green rows are hidden and the report is printed. I set up two custom views called "green rows hidden" and "green rows seen" to quickly switch back and forth between the two views. These reports are being used by a variety of people with widely varying computer skill. Despite frequent instruction some users regularly over-write formulas. I tried protecting the sheets, leaving the data entry cells unlocked, but when the sheets are protected, the custom views don't work and the green rows can't be hidden. What I would like to know is: is there a way to protect the formula cells and still be able to hide cells using the custom view function? Thanks in advance! Mike Gord Dibben MS Excel MVP -- burnsbyrne ------------------------------------------------------------------------ burnsbyrne's Profile: http://www.excelforum.com/member.php...o&userid=22153 View this thread: http://www.excelforum.com/showthread...hreadid=574142 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hiding rows while sheet is protected
Try this macro instead.
I don't like the beforeprint code. Sub Print_No_Greens() ActiveSheet.Unprotect Password:="justme" ActiveWorkbook.CustomViews("green rows hidden").Show ActiveWindow.SelectedSheets.PrintOut Copies:=1, _ Collate:=True ActiveWorkbook.CustomViews("green rows seen").Show ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub Since you're not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm In the meantime.............first make a backup of your original workbook. With your workbook open, hit ALT + F11 to get to the Visual Basic Editor. CTRL + r to open the Project Explorer. Select your workbook/project and right-click insert module Copy and paste the above code into that module. ALT + q to go back to Excel. ToolsMacroMacros. Select the Print_No_Greens macro and "Run" When happy, save the workbook. You can assign the print macro to a button that your users will click to print. Gord On Tue, 22 Aug 2006 13:39:08 -0400, burnsbyrne wrote: Gord, Thanks for your response. I neglected to mention in my question that I am a non-programmer. While I recognize what you wrote as computer code and I am very grateful for your efforts, I have no idea what to do with it. In addition, I need something that will work for other people who are even less computer literate than I am. I was hoping that there would be some option I could choose when protecting the sheet that would allow hiding and unhiding the rows. If you can explain what to do with the code without taking up too much of your time I'd appreciate it. Thank you again, Mike Gord Dibben Wrote: If you will use this code, please make amendments to it. Remove the line........... ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Gord On Tue, 22 Aug 2006 09:11:33 -0700, Gord Dibben <gorddibbATshawDOTca wrote: Unprotect the sheet, hide greens, re-protect the sheet. Something like this. Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.Unprotect Password:="justme" ActiveWorkbook.CustomViews("green rows hidden").Show ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ActiveWorkbook.CustomViews("green rows seen").Show ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub Right-click on the Excel Icon left of "File" on the menubar. Select "View Code" Copy/paste the BeforePrint code above into that module. Gord Dibben MS Excel MVP On Tue, 22 Aug 2006 08:13:18 -0400, burnsbyrne wrote: I have several spreadsheets that I put together for other people to enter data and report results. The sheets are made of about 25 sets with four rows in each set. Rows #3 and #4 are where data is entered. I have shaded them green to distinguish them from the rows that contain formulas. Rows #1 and #2 are formulas, row #1 is a percentage based on the numbers entered in rows 3 and 4, e.g. 75%. Row 2 is a fraction based on the numbers entered in rows 3 and 4, e.g. 3/4. When the sheet is used as a report the green rows are hidden and the report is printed. I set up two custom views called "green rows hidden" and "green rows seen" to quickly switch back and forth between the two views. These reports are being used by a variety of people with widely varying computer skill. Despite frequent instruction some users regularly over-write formulas. I tried protecting the sheets, leaving the data entry cells unlocked, but when the sheets are protected, the custom views don't work and the green rows can't be hidden. What I would like to know is: is there a way to protect the formula cells and still be able to hide cells using the custom view function? Thanks in advance! Mike Gord Dibben MS Excel MVP |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hiding rows while sheet is protected
Thanks, Gord. That will give me a little project to work on. Mike -- burnsbyrne ------------------------------------------------------------------------ burnsbyrne's Profile: http://www.excelforum.com/member.php...o&userid=22153 View this thread: http://www.excelforum.com/showthread...hreadid=574142 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
resetting last cell | Excel Discussion (Misc queries) | |||
Hiding Rows & Tabs at the same time | Excel Discussion (Misc queries) | |||
print area selection slows excel, formula for hiding rows?? | Excel Discussion (Misc queries) | |||
Copying multiple sheets from one book 2 another and undertake spec | Excel Discussion (Misc queries) | |||
how to hide rows in a protected sheet | Excel Worksheet Functions |