Disabling Page Breaks

[tony-heaton]

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

[Greg M]

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

[josephteh]

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

[tony-heaton]

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

[tony-heaton]

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

[josephteh]

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

[josephteh]

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

[tony-heaton]

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

[josephteh]

That's another problem!

[Greg M]

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

[Greg M]

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

[josephteh]

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