Excel Margins

[mdmackillop]

Hi
I'm creating a Userform for PDF printing and want to show the existing page setup. I can return Orientation and PaperSize. Is it possible to return Margin settings: Normal, Narrow, Wide and Custom?
Regards
MD

[mikerickson]

.BottomMargin etc are properties of the PageSetup object.

[Paul_Hossler]

You can get the settings in points and convert to inches to display on the UF

Then convert the inches back using Application.InchesToPoints to actually set them




Option Explicit

'http://answers.microsoft.com/en-us/office/forum/officeversion_other-excel/what-is-the-conversion-of-points-to-inches/c57b633a-3148-4820-ae6d-e391c0ab635f
' Height is measured in points (72 points to the inch)
' Width is measured in points (12 points to the inch)
' To make a chess board with 1 inch squares, select the entire worksheet and
' set all the row heights to 72 and column widths to 12
'BUT it seems margins use 72 and cells use 12 for width

Sub Macro1()
With ActiveSheet.PageSetup
MsgBox .LeftMargin / 72 ' = Application.InchesToPoints(0.7)
MsgBox .RightMargin / 72 ' = Application.InchesToPoints(0.7)
MsgBox .TopMargin / 72 ' = Application.InchesToPoints(0.75)
MsgBox .BottomMargin / 72 ' = Application.InchesToPoints(0.75)
MsgBox .HeaderMargin / 72 ' = Application.InchesToPoints(0.3)
MsgBox .FooterMargin / 72 ' = Application.InchesToPoints(0.3)
End With
End Sub



If you really want to map to Narrow, Wide, etc. I guess you'd need to test the .Pagesetup values against the MS defined ones to see if they all match

[mdmackillop]

I can provide option buttons to output to suitable settings, but running page setup slows things down, even when there is no change taking place. So if I'm set for narrow margins and I want narrow margins, I don't need to run page setup.
What I was looking for but can't find was any return value which related to the margin page setup names previously described. A vain hope, I know.

[Paul_Hossler]

I can provide option buttons to output to suitable settings, but running page setup slows things down, even when there is no change taking place. So if I'm set for narrow margins and I want narrow margins, I don't need to run page setup.
What I was looking for but can't find was any return value which related to the margin page setup names previously described. A vain hope, I know.

I believe that it's setting/changing a PageSetup property (since it has to talk to the printer driver) that takes the time.

Getting the property value is normally fast

What I do is to test the value and only change what needs to be changed


Rough Example:



if .LeftMargin / 72 <> 0.7 then .LeftMargin = Application.InchesToPoints(0.7)



So for something I did to configure WS's to be basically the same




Sub Default_PageSetup()
With ActiveSheet.PageSetup
If .PrintTitleRows <> "$1:$1" Then .PrintTitleRows = "$1:$1"
If .PrintTitleColumns <> vbNullString Then .PrintTitleColumns = vbNullString
If .PrintArea <> vbNullString Then .PrintArea = vbNullString

If .LeftHeader <> "&""Arial,Bold""&D - &T" Then .LeftHeader = "&""Arial,Bold""&D - &T"
If .CenterHeader <> vbNullString Then .CenterHeader = vbNullString
If .RightHeader <> "&""Arial,Bold""&A" Then .RightHeader = "&""Arial,Bold""&A"

If .LeftFooter <> "Page &P of &N" Then .LeftFooter = "Page &P of &N"
If .CenterFooter <> vbNullString Then .CenterFooter = vbNullString
If .RightFooter <> "&F" Then .RightFooter = "&F"

If .Orientation <> xlLandscape Then .Orientation = xlLandscape
If .Draft <> False Then .Draft = False
If .PaperSize <> xlPaperLetter Then .PaperSize = xlPaperLetter
If .FirstPageNumber <> xlAutomatic Then .FirstPageNumber = xlAutomatic
If .Order <> xlDownThenOver Then .Order = xlDownThenOver
If .BlackAndWhite <> False Then .BlackAndWhite = False
If .Zoom <> False Then .Zoom = False
If .FitToPagesWide <> 1 Then .FitToPagesWide = 1
If .FitToPagesTall <> False Then .FitToPagesTall = False
If .PrintErrors <> xlPrintErrorsDisplayed Then .PrintErrors = xlPrintErrorsDisplayed
End With
End Sub




If you really want to map to Narrow, Wide, etc. I guess you'd need to test the .Pagesetup values against the MS defined ones to see if they all match

[snb]

You might use:


Sub M_snb()
Application.PrintCommunication = False

y = InputBox("1 Normal" & vbLf & "2 Wide" & vbLf & "3 Narrow", "margins")
If Val(y) > 0 And Val(y) < 4 Then
sn = Array(Array(1, 1, 0.5), Array(0.25, 0.75, 0.3), Array(0.7, 0.75, 0.3))(y - 1)

With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(sn(0))
.RightMargin = Application.InchesToPoints(sn(0))
.TopMargin = Application.InchesToPoints(sn(1))
.BottomMargin = Application.InchesToPoints(sn(1))
.HeaderMargin = Application.InchesToPoints(sn(2))
.FooterMargin = Application.InchesToPoints(sn(2))
End With
End If

Application.PrintCommunication = True
End Sub

Although the Normal /Wide & Narrow give the impression to be some 'presets', I couldn't find such presets as combination.

[mdmackillop]

Thanks all.
I think I can make use of SNB's arrays most easily (despite the deliberate error :thumb)

Sub MD_snb()
Dim sn, md, x, Margins
Dim y As String
Dim i As Long



Application.PrintCommunication = False


'Get margin settings
With ActiveSheet.PageSetup
md = Array(.LeftMargin / 72, .TopMargin / 72, .FooterMargin / 72)
End With


x = Array(Array(0.7, 0.75, 0.3), Array(0.25, 0.25, 0.3), Array(1, 1, 0.5))


'Compare margins (only 3 checked in test)
For i = 0 To 2
If CBool((md(0) = x(i)(0)) * (md(1) = x(i)(1)) * (md(2) = x(i)(2))) Then Exit For
Next


'Result
If i = 3 Then
MsgBox "Custom margins"
Else
Margins = Array("Normal", "Narrow", "Wide")
MsgBox "Margins are " & Margins(i) & vbCr & md(0) & "/" & md(1) & "/" & md(2)
End If


' Set margins
y = InputBox("1 Normal" & vbLf & "2 Narrow" & vbLf & "3 Wide" & vbCr & "Leave blank to exit", "margins")


If y = "" Then Exit Sub


If Val(y) > 0 And Val(y) < 4 Then
sn = Array(Array(0.7, 0.75, 0.3), Array(0.25, 0.25, 0.3), Array(1, 1, 0.5))(y - 1)


With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(sn(0))
.RightMargin = Application.InchesToPoints(sn(0))
.TopMargin = Application.InchesToPoints(sn(1))
.BottomMargin = Application.InchesToPoints(sn(1))
.HeaderMargin = Application.InchesToPoints(sn(2))
.FooterMargin = Application.InchesToPoints(sn(2))
End With
End If


Application.PrintCommunication = True
Call MD_snb
End Sub

[snb]

We can avoid the calculating:


y = InputBox("1 Normal" & vbLf & "2 Wide" & vbLf & "3 Narrow", "margins")
If Val(y) > 0 And Val(y) < 4 Then
sn = Array(Array(72, 72, 36), Array(18, 54, 21.6), Array(50.4, 54, 21.6))

With ActiveSheet.PageSetup
.LeftMargin = sn(0)
.RightMargin = sn(0)
.TopMargin = sn(1)
.BottomMargin = sn(1)
.HeaderMargin = sn(2)
.FooterMargin = sn(2)
End With
End If

[Paul_Hossler]

Any reason your sub calls itself at the end?




......
Application.PrintCommunication = True
Call MD_snb
End Sub

[mdmackillop]

Hi Paul
This is just a test routine to demonstrate that the changes are recognised and correct.

[SamT]

KB Article?

[mdmackillop]

KB is not working at present. I've written to Admin.