+ Reply to Thread
Results 1 to 12 of 12

Disabling Page Breaks

  1. #1
    Registered User
    Join Date
    06-23-2021
    Location
    Windsor UK
    MS-Off Ver
    Office365
    Posts
    15

    Disabling Page Breaks

    Greetings,

    Wonder if someone could point me in the right direction.

    I have a blank Test Result document written in excel (macro enabled for imported graphics placement and printing as a pdf) and have it formatted so it prints correctly. [This a mirror image of the Master Word Document]

    However as this is a master document and is copied by other members of the test team to run on their laptops, I have it password protected so they cannot enter any data except for where the results should go.

    But.... no matter what I try to do, Page Break is always active and if a member of the team starts putting in extra page breaks, it messes up the whole pdf as the graphics can split over 2 pages, page numbering then is incorrect etc etc.

    Is there any way that I can inhibit the page breaks unless the excel file is unlocked.

    I have spent some time searching for a solution and unable to find exactly what I need.

    Thanks in advance for any guidance,

    Tony

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,481

    Re: Disabling Page Breaks

    Hi there,

    Interesting! I didn't realise that page breaks could be inserted on a protected worksheet

    Quick question - are you dealing with a single worksheet or with multiple worksheets?

    Also, are you worried about only horizontal page breaks, or are vertical page breaks a problem as well?

    Regards,

    Greg M
    Last edited by Greg M; 04-09-2024 at 12:55 PM. Reason: Second question added

  3. #3
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    3,091

    Re: Disabling Page Breaks

    Does setting the print scaling to 1 page wide and 1 page tall help?

  4. #4
    Registered User
    Join Date
    06-23-2021
    Location
    Windsor UK
    MS-Off Ver
    Office365
    Posts
    15

    Re: Disabling Page Breaks

    Hi Greg, the file has 3 sheets, but only a single worksheet needs to be printed. The other sheets are info ( ie Read Me and a Data sheet, this is normally hidden).

    If you right click on the row number, most of the options are "greyed out" but insert page break and reset page break are still there and functional.

    Doesn't have any Vertical breaks ( never come across this !).

    Hi Joseph, unfortunately it's a 17 page document that has to be printed out so it conforms with the Word version.

    Settings are: Scaling 72%, width 1 page, height automatic.

    I've tried a few options but nothing seems to work....

    But thanks for your help guys,

    Tony

  5. #5
    Registered User
    Join Date
    06-23-2021
    Location
    Windsor UK
    MS-Off Ver
    Office365
    Posts
    15

    Re: Disabling Page Breaks

    Just had a Damascus moment.....

    Unchecking View headings removes the column and row numbers.... users now unable to right click on row number.....

    Not elegant but it seems to work. Time to try and let it loose on my colleagues...

    Thanks again

  6. #6
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    3,091

    Re: Disabling Page Breaks

    Not foolproof as people can still go to page layout to insert page break.

  7. #7
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    3,091

    Re: Disabling Page Breaks

    If you could fix the number of page tall, maybe it could work.

  8. #8
    Registered User
    Join Date
    06-23-2021
    Location
    Windsor UK
    MS-Off Ver
    Office365
    Posts
    15

    Re: Disabling Page Breaks

    Hi Joseph,

    Thanks.... I didn't think of that, BUT setting the number of pages to 17 seems to do the trick. Just need to be able to lock that option from the other users.



    Thanks again

    Tony

  9. #9
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    3,091

    Re: Disabling Page Breaks

    That's another problem!

  10. #10
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,481

    Re: Disabling Page Breaks

    Hi Tony,

    Quick reply from my phone - I MIGHT have a more "targeted" solution, but I'm going out now. Hope to be able to post something later today.

    Regards

    Greg M

  11. #11
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,481

    Re: Disabling Page Breaks

    Hi again,

    See if the approach used in the attached workbook does what you need.

    When the workbook is opened a new temporary hidden worksheet is created. The locations of all of the horizontal page breaks on worksheet "Sheet To Be Protected" are stored in the temporary worksheet.

    Before the protected worksheet is printed, all horizontal page breaks (including any inserted by the User) on the worksheet are deleted, and all (i.e. original) horizontal page breaks whose locations are stored in the temporary worksheet are restored.

    When the workbook is closed, the temporary worksheet is automatically deleted.


    The following code is inserted in the "ThisWorkbook" VBA CodeModule:
    Please Login or Register  to view this content.

    The following code is inserted in a standard VBA CodeModule:
    Please Login or Register  to view this content.
    The highlighted values may be changed to suit your own requirements.


    Hope this helps - please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files
    Last edited by Greg M; 04-10-2024 at 07:52 PM. Reason: Typo corrected

  12. #12
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    3,091

    Re: Disabling Page Breaks

    Another way is to save the print setting in custom view and retrieve that custom view whenever you want to print.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Page breaks in spreadsheet do not page breaks that I insert.
    By tfurnivall in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-08-2023, 09:52 PM
  2. [SOLVED] VB code to do page setup and find page breaks
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-14-2019, 06:37 PM
  3. Replies: 1
    Last Post: 06-28-2017, 11:28 AM
  4. Clear all page breaks, then insert new breaks at change in data
    By Alex0929 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-09-2014, 03:12 PM
  5. Removing Existing Page Breaks and Inserting a Page Break every third Total Line
    By Kalvas in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-17-2013, 05:12 AM
  6. Replies: 1
    Last Post: 03-15-2009, 06:08 PM
  7. Hard page breaks being replaced by soft breaks in the wrong place
    By JDavies in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-14-2008, 10:28 AM

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