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

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

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

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

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

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