complicated hide/unhide rows on protected sheet

**achohan** · 06-07-2012, 11:07 AM

Hi, i am creating a template that has various cells that are locked to ensure the formulas stay intact. I protect the sheet and disallow selection of locked cells. This way the user doesnt have to click on everything and try to figure out where they are to input information and where they arent. This was all fine now i am trying to print it and this thing wont even fit properly on a 11x17 page and so therefore i now am trying to hide some of the excess rows. for example i have a part that has 15 rows of inputting data, they will probably only need 5 rows most of the time but i want to leave the extra rows there in order to allow for change on the fly. Now my question is when i protect it and turn selection of locked cells off, i can not hide or unhide rows. I can hide and unhide them if select locked cells is enabled however but like i stated it complicates it for users when they are inputting data. Any work arounds? Thank you very much

**JBeaucaire** · 06-07-2012, 12:31 PM

You could install a BeforePrint macro into the workbook that goes through the sheet and hides unused rows, prints, then unhides the rows again... Is VBA ok?

Otherwise you will be manually unprotecting and hiding rows.

If VBA is ok, post your workbook and point out the sections of rows where you want this evaluation to occur. Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

**achohan** · 06-07-2012, 01:33 PM

Budget Tracking.xlsm

here is the file, VBA is fine although i have no clue what that is. I marked the rows that i want to appear and disappear when printing by putting a yellow fill. Thank you very much your help is greatly appreciated.

**JBeaucaire** · 06-07-2012, 02:38 PM

I added some formulas into your column Y yellow cells that will put an "X" in the rows that are to be hidden.
I added a BeforePrint macro into your ThisWorkbook module which will hide all the rows with text showing in column Y, print, then unhide.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Application.EnableEvents = False
    Cancel = True
    With Sheets("Budget Sheet")
        .Range("Y:Y").SpecialCells(xlCellTypeFormulas, 2).EntireRow.Hidden = True
        .PrintOut
        .Range("Y:Y").SpecialCells(xlCellTypeFormulas, 2).EntireRow.Hidden = False
    End With
    Application.EnableEvents = True
End Sub

If you're satisfied with the results, you can hide column Y.

**achohan** · 06-07-2012, 02:52 PM

When i go to print i get and the below error

run time error 1004
unable to get the specialcells of the range class

**JBeaucaire** · 06-07-2012, 07:33 PM

Change the macro IN the workbook to the same as the one posted above.

**achohan** · 06-08-2012, 08:23 AM

Originally Posted by JBeaucaire

Change the macro IN the workbook to the same as the one posted above.

wow, it works!! When i click print i cant select which printer i want to print to or select paper sizes and if i want it in color. any suggestions?

**JBeaucaire** · 06-08-2012, 09:15 AM

record a macro of you printing the workbook with all the settings you want. You'll need to add that into this macro.

**achohan** · 06-08-2012, 12:02 PM

only issue with that is i have no clue how to do that, let alone what a macro exactly is.

sorry

**JBeaucaire** · 06-08-2012, 01:12 PM

Go to File > Print and set all your desired print settings. Then you can use your print icon.

**achohan** · 06-08-2012, 01:27 PM

do you mean go to the file>print>print preview because if i click on print it just directly prints a page. If i go to print preview i can set paper size and orientation and stuff but cant select which printer i want. And since this is going to be a template it will be going to a lot of people who will have various printers.

**achohan** · 06-21-2012, 02:27 PM

Originally Posted by JBeaucaire

Go to File > Print and set all your desired print settings. Then you can use your print icon.

Hi again, i need to copy this sheet into a different workbook, but when i transfer it the printing macro doesn't follow. Do you know how to transfer it?

**JBeaucaire** · 06-08-2012, 02:26 PM

You're talking like you're using Excel 2003. Your profile says 2007. On the Ribbon you can click File, then click the Print menu on the sidebar:

http://screencast.com/t/IJbfePdDglI

**achohan** · 06-08-2012, 02:41 PM

no i have office 2007 because when i open it, it says of excel 2007, word 2007 etc. but from the picture u sent me my office button doesnt show the same menu as you have shown.

My office button brings this menu style

http://www.spbasic.com/NewImages/Dow...celOptions.png

**achohan** · 06-08-2012, 02:44 PM

http://www.java2s.com/Tutorial/Micro..._To_Print_.PNG

this is what my print menu shows and when i click on print it just prints directly no options.

btw i greatly appreciate you taking the time to help me out, this printing thing will save my manager a whole lot of work and make this template a lot more efficient.

**JBeaucaire** · 06-08-2012, 03:44 PM

I can't really help you on your Print settings as I don't have that version of Excel. I can't believe you don't have some way you can access PRINT settings without actually printing something. Perhaps under Page Setup? Keep hunting!

If that takes care of your originall posted need, please select Thread Tools from menu above and set this particular topic to SOLVED.

complicated hide/unhide rows on protected sheet

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

complicated hide/unhide rows on protected sheet

Re: complicated hide/unhide rows on protected sheet

Re: complicated hide/unhide rows on protected sheet

Re: complicated hide/unhide rows on protected sheet

Re: complicated hide/unhide rows on protected sheet

Re: complicated hide/unhide rows on protected sheet

Re: complicated hide/unhide rows on protected sheet

Re: complicated hide/unhide rows on protected sheet

Re: complicated hide/unhide rows on protected sheet

Re: complicated hide/unhide rows on protected sheet

Re: complicated hide/unhide rows on protected sheet

Re: complicated hide/unhide rows on protected sheet

Re: complicated hide/unhide rows on protected sheet

Re: complicated hide/unhide rows on protected sheet

Re: complicated hide/unhide rows on protected sheet

Re: complicated hide/unhide rows on protected sheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions