Hiding rows while sheet is protected

[burnsbyrne]

I have several spreadsheets that I put together for other people t
enter data and report results. The sheets are made of about 25 set
with four rows in each set. Rows #3 and #4 are where data i
entered. I have shaded them green to distinguish them from the row
that contain formulas. Rows #1 and #2 are formulas, row #1 is
percentage based on the numbers entered in rows 3 and 4, e.g. 75%. Ro
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 th
report is printed. I set up two custom views called "green row
hidden" and "green rows seen" to quickly switch back and forth betwee
the two views.

These reports are being used by a variety of people with widely varyin
computer skill. Despite frequent instruction some users regularl
over-write formulas. I tried protecting the sheets, leaving the dat
entry cells unlocked, but when the sheets are protected, the custo
views don't work and the green rows can't be hidden. What I would lik
to know is: is there a way to protect the formula cells and still b
able to hide cells using the custom view function?

Thanks in advance!

Mik

 

[Gord Dibben]

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

 

[Gord Dibben]

If you will use this code, please make amendments to it.

Remove the line...........

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True


Gord

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

Gord Dibben MS Excel MVP

 

[burnsbyrne]

Gord,
Thanks for your response. I neglected to mention in my question that
am a non-programmer. While I recognize what you wrote as computer cod
and I am very grateful for your efforts, I have no idea what to do wit
it. In addition, I need something that will work for other people wh
are even less computer literate than I am. I was hoping that ther
would be some option I could choose when protecting the sheet tha
would allow hiding and unhiding the rows.
If you can explain what to do with the code without taking up too muc
of your time I'd appreciate it.
Thank you again,
Mike

 

[Gord Dibben]

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.

Tools>Macro>Macros. 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

 

[burnsbyrne]

Thanks, Gord. That will give me a little project to work on.
Mike