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