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
<[email protected]> 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

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
><[email protected]> 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

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

On Tue, 22 Aug 2006 13:39:08 -0400, burnsbyrne
<[email protected]> 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
>> ><[email protected]> 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