Solved: Increment page margins click at a time

[Dave T]

Hello All,

Just curious if this can be done...

I regularly receive a word report (2003) produced by another application with preset page margins. I use the following macro to change them to better suit my needs:


Sub HLRPageSetup()
' Resets page margins of standard HLR output
'
With Selection.PageSetup
.Orientation = wdOrientPortrait
.PageWidth = CentimetersToPoints(21)
.PageHeight = CentimetersToPoints(29.7)
.TopMargin = CentimetersToPoints(2)
.BottomMargin = CentimetersToPoints(2)
.LeftMargin = CentimetersToPoints(2.5)
.RightMargin = CentimetersToPoints(2.5)
.HeaderDistance = CentimetersToPoints(1)
.FooterDistance = CentimetersToPoints(1)
End With
ActiveWindow.ActivePane.View.Zoom.Percentage = 120
End Sub

Is it possible to create another macro that could increase or decrease the page margins each time a button is clicked? For example the left and right margins go from 3.0cm to 2.9cm and so on (i.e. 0.1cm each time a button is clicked). I often tweak to margins to get the report to fit on full pages i.e. not have 1-2 lines on a new page.

An example of what I would like is what I occasionally use with paragraphs:



Sub SpaceAfter6()
' Adds six points of space after selected text each time you click it
'
Selection.ParagraphFormat.SpaceAfter = Selection.ParagraphFormat.SpaceAfter + 6
End Sub

Any feedback would be appreciated.
Regards,
Dave T

[macropod]

Hi Dave,

Pretty easy, really. Given that you've already got code doing something similar, you should have been able to figure it out for yourself:
Sub HLRPageSetup2()
' Increments page margins of standard HLR output
Dim Delta As Integer
Delta = InputBox("Input '1' to increase, '-1' to decrease margins", "Margin Changer")
If Not IsNumeric(Delta) Then Exit Sub
If Abs(Delta) > 1 Then Exit Sub
With Selection.PageSetup
.Orientation = wdOrientPortrait
.PageWidth = .PageWidth
.PageHeight = .PageHeight
.TopMargin = .TopMargin + CentimetersToPoints(0.1 * Delta)
.BottomMargin = .BottomMargin + CentimetersToPoints(0.1 * Delta)
.LeftMargin = .LeftMargin + CentimetersToPoints(0.125 * Delta)
.RightMargin = .RightMargin + CentimetersToPoints(0.125 * Delta)
.HeaderDistance = .HeaderDistance
.FooterDistance = .HeaderDistance
End With
End SubNote: the above allows you to go in either direction. If you want to be able to work in larger multiples, you could change the value in ' If Abs(Delta) > 1 Then Exit Sub' - or even delete the line altogether - and change the input message to suit. I've also left in the lines for other units that probably shouldn't be changed. These lines are thus redundant and can be deleted if you don't want to make the same kinds of changes to them also.

[Dave T]

Sorry, unable to delete post error

[Dave T]

Hello macropod,

Thank you very much for the reply and yes I did have an attempt, but it was not along the lines you went.
I was trying things along the lines of .LeftMargin = CentimetersToPoints(2.5) + 0.1 and .LeftMargin = CentimetersToPoints(2.5) = .LeftMargin = CentimetersToPoints(2.5) + 0.1 without success.

Is there a way to bypass the message box and just have a toolbar button that each time it is clicked the margins reduce a set increment per click.
Sometimes the text in a paragraph has one to many words and is on 2 lines and 2 pages, so by changing the margins the long/normal way the text is now all on one line thereby ensuring the text all fits on one page.

If you could change the left and right margins click by click you could very quickly see the text change.

Thanks again for the reply.

Regards,
David

[macropod]

Hi David,

Sub HLRPageSetup3()
' Increments page margins of standard HLR output
With Selection.PageSetup
.TopMargin = .TopMargin + CentimetersToPoints(0.1)
.BottomMargin = .BottomMargin + CentimetersToPoints(0.1)
.LeftMargin = .LeftMargin + CentimetersToPoints(0.125)
.RightMargin = .RightMargin + CentimetersToPoints(0.125)
End With
End Sub

I'll leave it to you to create the toolbar button - the instructions in Excel's Help file should be sufficient.

[Dave T]

Fantastic...

I was so close yet so far.
Greatly appreciated.

Regards,
David

[fumei]

Ack. Whatever happened to styles?

[macropod]

Hi Gerry,

I don't know of any Style that will change the page's margins ...

I'll freely admit, though, that reducing font sizes by 0.5pt at a time can achieve the desired result. If the resulting changes in appearance need to be propagated across paragraphs with different font specs, this can be quite time consuming - and the 0.5pt increments might be a bit drastic.

[Dave T]

Hello Gerry,

Unfortunately the program I use produces a report in html format and most of the formatting within the report has been manual applied.

The page margin macro from macropod enables me to ‘tweak’ the margins, which in turn means text that was on two lines can be reduced to one line which often means the report can be reduced from two pages to a single page.

I certainly do not need to be convinced of the merits of styles within documents. Within all of the templates I produce I have setup all the styles and for editing this is the only way to go.

The biggest problem I have is that despite my efforts to create the styles and educate people about styles I would say that most of the people I work with do not seem to know about styles.

I have even created a toolbar attached to the documents with toolbar buttons to apply the styles yet most seem to use to manually formatting.

Anyway... thanks for both of your comments.

Regards,
David

[fumei]

Sorry. I certainly was not trying to be critical, and I totally synpathize with your comment re: getting people to use styles properly.

[Dave T]

That's OK Gerry, I never took it as you being critical.
I actually had a chuckle and I agree with your frustration about styles not being used effectively.

Regards,
David

[Dave T]

Hello All,

Can the macro supplied by Paul in post #5 have some extra additions to prevent the incremental margins being smaller than a certain minimum size ???

For example if the left or right or top or bottom margins were to get smaller than say a 1.0cm a 'print margin' error might be starting to happen.
Can Paul's macro incrementally go from margins of 2.5cm down a click at a time but not let any margins be smaller than 1.0cm ???

Just curious if this can be done.

Regards,
Dave T

[macropod]

I'm not quite sure what you want here, since the macro in post #5 increases the margin size. You can limit each margin's maximum via an If test. For example:
If Format(.TopMargin, "0.0") < CentimetersToPoints(2.5) Then .TopMargin = .TopMargin + CentimetersToPoints(0.1)

[Dave T]

Hello Paul,

Thank you very much for pointing me in the right direction.

Here is a copy of what I did:


Sub Smaller_Margins_L_R_V2()
' Incrementally reduces left and right page margins by 0.1 cm
With Selection.PageSetup
If .LeftMargin > CentimetersToPoints(1.0) Then .LeftMargin = .LeftMargin - CentimetersToPoints(0.1)
If .RightMargin > CentimetersToPoints(1.0) Then .RightMargin = .RightMargin - CentimetersToPoints(0.1)
End With
End Sub

I appreciate your quick response.

Regards,
Dave T