View Full Version : [SOLVED:] Excel Margins
mdmackillop
06-08-2016, 05:57 AM
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
06-08-2016, 06:45 AM
.BottomMargin etc are properties of the PageSetup object.
Paul_Hossler
06-08-2016, 06:48 AM
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
06-08-2016, 07:04 AM
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
06-08-2016, 07:17 AM
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
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
06-08-2016, 10:08 AM
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
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
06-08-2016, 02:54 PM
Any reason your sub calls itself at the end?
......
Application.PrintCommunication = True
Call MD_snb
End Sub
mdmackillop
06-08-2016, 03:16 PM
Hi Paul
This is just a test routine to demonstrate that the changes are recognised and correct.
mdmackillop
06-09-2016, 12:17 AM
KB is not working at present. I've written to Admin.
Powered by vBulletin® Version 4.2.5 Copyright © 2025 vBulletin Solutions Inc. All rights reserved.