+ Reply to Thread
Results 1 to 16 of 16

complicated hide/unhide rows on protected sheet

  1. #1
    Registered User
    Join Date
    05-29-2012
    Location
    Toronto,ON
    MS-Off Ver
    Excel 2007
    Posts
    14

    Exclamation complicated hide/unhide rows on protected sheet

    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

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: complicated hide/unhide rows on protected sheet

    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.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    05-29-2012
    Location
    Toronto,ON
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: complicated hide/unhide rows on protected sheet

    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.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: complicated hide/unhide rows on protected sheet

    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.

    Please Login or Register  to view this content.
    If you're satisfied with the results, you can hide column Y.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-29-2012
    Location
    Toronto,ON
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: complicated hide/unhide rows on protected sheet

    When i go to print i get and the below error

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

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: complicated hide/unhide rows on protected sheet

    Change the macro IN the workbook to the same as the one posted above.
    Attached Files Attached Files
    Last edited by JBeaucaire; 06-07-2012 at 07:35 PM.

  7. #7
    Registered User
    Join Date
    05-29-2012
    Location
    Toronto,ON
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: complicated hide/unhide rows on protected sheet

    Quote Originally Posted by JBeaucaire View Post
    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?

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: complicated hide/unhide rows on protected sheet

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

  9. #9
    Registered User
    Join Date
    05-29-2012
    Location
    Toronto,ON
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: complicated hide/unhide rows on protected sheet

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

  10. #10
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: complicated hide/unhide rows on protected sheet

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

  11. #11
    Registered User
    Join Date
    05-29-2012
    Location
    Toronto,ON
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: complicated hide/unhide rows on protected sheet

    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.

  12. #12
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: complicated hide/unhide rows on protected sheet

    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

  13. #13
    Registered User
    Join Date
    05-29-2012
    Location
    Toronto,ON
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: complicated hide/unhide rows on protected sheet

    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

  14. #14
    Registered User
    Join Date
    05-29-2012
    Location
    Toronto,ON
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: complicated hide/unhide rows on protected sheet

    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.

  15. #15
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: complicated hide/unhide rows on protected sheet

    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.

  16. #16
    Registered User
    Join Date
    05-29-2012
    Location
    Toronto,ON
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: complicated hide/unhide rows on protected sheet

    Quote Originally Posted by JBeaucaire View Post
    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?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1