Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 6th 16, 03:58 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2016
Posts: 8
Default Margins and cell widths

I would be grateful for any advice on how to set margins and cell widths
in Excel so that when the document is printed the printed document
reflects, accurately, the settings made.

I have tried using VBA and also the ribbon facilities, but in both cases
my settings do not accord with a subsequent print except to the extent
that the larger the setting the larger the width.

Kind regards

Brian



  #2   Report Post  
Old February 6th 16, 06:17 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,182
Default Margins and cell widths

I would be grateful for any advice on how to set margins and cell
widths in Excel so that when the document is printed the printed
document reflects, accurately, the settings made.

I have tried using VBA and also the ribbon facilities, but in both
cases my settings do not accord with a subsequent print except to the
extent that the larger the setting the larger the width.

Kind regards

Brian


Some suggestions...

Setting a zero margin will always result a default 0.25 margin;
This is built into your printer so not much you can do about this
unless you can send commands to it to do otherwise.

Set header/footer margins to ALWAYS be less than top/bottom margins;

Do all settings in PrintPreview so Excel displays page boundaries after
closing that window. Now you can...

Adjust column widths to suit your desired layout for the displayed page
boundaries.

Note that setting Print_Area will affect page boundaries, so you need
to establish what range of columns you want to fit on a printed page. I
always set width of the 1st col to the right to 1 or 2 so I know where
the right limit is if the page boundaries from PrintPreview haven't
been set yet; -usually after opening the file, and before previewing
the 1st time during a session.

When designing forms, note that FontSize affects RowHeight unless you
manually specify row height for specific rows. My default font is Arial
8pt just because I like that font, and that size gives me 30 columns of
screen real estate at the default col width. This, of course, varies
with display size; the above is for my 17" laptop w/1920x1200
resolution. This 13" laptop only gives me 24 cols at 1366x768
resolution. (Zoom=100%)

I do a lot of authoring in Excel and following these guidelines has
always given me predictable, reliable printing results.
HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #3   Report Post  
Old February 7th 16, 03:27 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2016
Posts: 8
Default Margins and cell widths

On 06/02/2016 18:17, GS wrote:
I would be grateful for any advice on how to set margins and cell
widths in Excel so that when the document is printed the printed
document reflects, accurately, the settings made.

I have tried using VBA and also the ribbon facilities, but in both
cases my settings do not accord with a subsequent print except to the
extent that the larger the setting the larger the width.

Kind regards

Brian


Some suggestions...

Setting a zero margin will always result a default 0.25 margin;
This is built into your printer so not much you can do about this
unless you can send commands to it to do otherwise.

Set header/footer margins to ALWAYS be less than top/bottom margins;

Do all settings in PrintPreview so Excel displays page boundaries after
closing that window. Now you can...

Adjust column widths to suit your desired layout for the displayed page
boundaries.

Note that setting Print_Area will affect page boundaries, so you need to
establish what range of columns you want to fit on a printed page. I
always set width of the 1st col to the right to 1 or 2 so I know where
the right limit is if the page boundaries from PrintPreview haven't been
set yet; -usually after opening the file, and before previewing the 1st
time during a session.

When designing forms, note that FontSize affects RowHeight unless you
manually specify row height for specific rows. My default font is Arial
8pt just because I like that font, and that size gives me 30 columns of
screen real estate at the default col width. This, of course, varies
with display size; the above is for my 17" laptop w/1920x1200
resolution. This 13" laptop only gives me 24 cols at 1366x768
resolution. (Zoom=100%)

I do a lot of authoring in Excel and following these guidelines has
always given me predictable, reliable printing results.
HTH



Thanks Garry for your comments. Very helpful.

I have done some more experiments and my results are as follows:-

If the margin is set at Mmm (using the ribbon) then the actual margin is
(M - 3)mm.

If the column width is set at Wmm, then the actual column width
is (1.8 x W)mm.

The column width can be set either using the ribbon or using
"Worksheets("Trial").Columns("A").ColumnWidth" in VBA. Each method
produces the same results.

If printing is done showing the column of row numbers, then the data
columns are, in effect, shifted to the right a little bit (about 5 or
6mm using a 10pt font).

The margin setting corresponds to what you said about margins being
reduced by 0.25, but the column width setting is a mystery.

The net result is that I can now set margins and columns widths using
VBA fairly accurately.

Thanks for your help.

Regards

Brian




  #4   Report Post  
Old February 7th 16, 06:24 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,182
Default Margins and cell widths

That's great, Brian! Glad to help and I appreciate the feedback.

I know the RowHeight is font-dependant and so is the reason the value
doesn't 'jive' with ColumnWidth. For example, to set up a sheet for
creating forms I start with a graph paper layout grid. Using Arial 8pt
as my default font...

RowHt = 12.00
ColWd = 2.00

...to get square-ish cells so I can manage options as checkboxes or
choices directly on the form via Worksheet events. Naturally the
gridlines are turned off so I use the 'dotted' border style for field
input areas. My point is.., changing to a taller font or larger font
size makes the cells more rectangular vertically and so messes up the
square appearance of my bordered option cells.<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #5   Report Post  
Old February 8th 16, 10:41 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2016
Posts: 8
Default Margins and cell widths

On 07/02/2016 18:24, GS wrote:
That's great, Brian! Glad to help and I appreciate the feedback.

I know the RowHeight is font-dependant and so is the reason the value
doesn't 'jive' with ColumnWidth. For example, to set up a sheet for
creating forms I start with a graph paper layout grid. Using Arial 8pt
as my default font...

RowHt = 12.00
ColWd = 2.00

..to get square-ish cells so I can manage options as checkboxes or
choices directly on the form via Worksheet events. Naturally the
gridlines are turned off so I use the 'dotted' border style for field
input areas. My point is.., changing to a taller font or larger font
size makes the cells more rectangular vertically and so messes up the
square appearance of my bordered option cells.<g


The real point is that Excel is not a layout program. If one is really
serious about the final print layout one should use something like
Crystal Reports or possibly InDesign. I have used Crystal Reports - I
found it very difficult but it is an amazing program - with it one can
achieve the precise layout that one wants extracting data from one or
more Excel spreadsheets, correct to a fraction of a millimetre. (By
the way, Crystal Reports is very expensive!). Using Excel for print
layout will always be for the poor man, poor in time and poor in money!

Regards

Brian






  #6   Report Post  
Old February 8th 16, 02:17 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,182
Default Margins and cell widths

On 07/02/2016 18:24, GS wrote:
That's great, Brian! Glad to help and I appreciate the feedback.

I know the RowHeight is font-dependant and so is the reason the
value
doesn't 'jive' with ColumnWidth. For example, to set up a sheet for
creating forms I start with a graph paper layout grid. Using Arial
8pt
as my default font...

RowHt = 12.00
ColWd = 2.00

..to get square-ish cells so I can manage options as checkboxes or
choices directly on the form via Worksheet events. Naturally the
gridlines are turned off so I use the 'dotted' border style for
field
input areas. My point is.., changing to a taller font or larger
font
size makes the cells more rectangular vertically and so messes up
the
square appearance of my bordered option cells.<g


The real point is that Excel is not a layout program. If one is
really serious about the final print layout one should use something
like Crystal Reports or possibly InDesign. I have used Crystal
Reports - I found it very difficult but it is an amazing program -
with it one can achieve the precise layout that one wants extracting
data from one or more Excel spreadsheets, correct to a fraction of a
millimetre. (By the way, Crystal Reports is very expensive!).
Using Excel for print layout will always be for the poor man, poor in
time and poor in money!

Regards

Brian


Um.., I basically disagree with your assessment of Excel as a layout
program. The precision is in pixels, and is very accurate. So unless
you're doing counterfeiting, I can't imagine why anyone would need
finer precision or another app for reporting.

As for time.., well that depends on skill level. Poor in time means
'don't have' and so suggests using your other apps is more time
consuming. On average, it takes less than 1 hour to publish a 1700-1800
row worksheet as an ebook complete with TOC (bookmarks) navigation,
images, and internal cross links.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #7   Report Post  
Old February 8th 16, 03:13 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2016
Posts: 8
Default Margins and cell widths

On 08/02/2016 14:17, GS wrote:
On 07/02/2016 18:24, GS wrote:
That's great, Brian! Glad to help and I appreciate the feedback.

I know the RowHeight is font-dependant and so is the reason the value
doesn't 'jive' with ColumnWidth. For example, to set up a sheet for
creating forms I start with a graph paper layout grid. Using Arial 8pt
as my default font...

RowHt = 12.00
ColWd = 2.00

..to get square-ish cells so I can manage options as checkboxes or
choices directly on the form via Worksheet events. Naturally the
gridlines are turned off so I use the 'dotted' border style for field
input areas. My point is.., changing to a taller font or larger font
size makes the cells more rectangular vertically and so messes up the
square appearance of my bordered option cells.<g


The real point is that Excel is not a layout program. If one is
really serious about the final print layout one should use something
like Crystal Reports or possibly InDesign. I have used Crystal
Reports - I found it very difficult but it is an amazing program -
with it one can achieve the precise layout that one wants extracting
data from one or more Excel spreadsheets, correct to a fraction of a
millimetre. (By the way, Crystal Reports is very expensive!). Using
Excel for print layout will always be for the poor man, poor in time
and poor in money!

Regards

Brian


Um.., I basically disagree with your assessment of Excel as a layout
program. The precision is in pixels, and is very accurate. So unless
you're doing counterfeiting, I can't imagine why anyone would need finer
precision or another app for reporting.

As for time.., well that depends on skill level. Poor in time means
'don't have' and so suggests using your other apps is more time
consuming. On average, it takes less than 1 hour to publish a 1700-1800
row worksheet as an ebook complete with TOC (bookmarks) navigation,
images, and internal cross links.


On reflection I guess you are correct. I have very little knowledge of
Excel and only a little more of Crystal. Further, although I use to be
a C programmer before I retired, I know very little about VBA. Hence,
I think my comments were ill-judged. You are, of course, absolutely
right also about time. Time equals experience. I have spent many
hours trying to write a simple vba routine which I could have written in
a few minutes in C.

Kind regards

Brian







  #8   Report Post  
Old February 8th 16, 05:42 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,182
Default Margins and cell widths

On 08/02/2016 14:17, GS wrote:
On 07/02/2016 18:24, GS wrote:
That's great, Brian! Glad to help and I appreciate the feedback.

I know the RowHeight is font-dependant and so is the reason the
value
doesn't 'jive' with ColumnWidth. For example, to set up a sheet
for
creating forms I start with a graph paper layout grid. Using
Arial 8pt
as my default font...

RowHt = 12.00
ColWd = 2.00

..to get square-ish cells so I can manage options as checkboxes
or
choices directly on the form via Worksheet events. Naturally the
gridlines are turned off so I use the 'dotted' border style for
field
input areas. My point is.., changing to a taller font or larger
font
size makes the cells more rectangular vertically and so messes up
the
square appearance of my bordered option cells.<g


The real point is that Excel is not a layout program. If one is
really serious about the final print layout one should use
something
like Crystal Reports or possibly InDesign. I have used Crystal
Reports - I found it very difficult but it is an amazing program -
with it one can achieve the precise layout that one wants
extracting
data from one or more Excel spreadsheets, correct to a fraction of
a
millimetre. (By the way, Crystal Reports is very expensive!).
Using
Excel for print layout will always be for the poor man, poor in
time
and poor in money!

Regards

Brian


Um.., I basically disagree with your assessment of Excel as a
layout
program. The precision is in pixels, and is very accurate. So
unless
you're doing counterfeiting, I can't imagine why anyone would need
finer
precision or another app for reporting.

As for time.., well that depends on skill level. Poor in time means
'don't have' and so suggests using your other apps is more time
consuming. On average, it takes less than 1 hour to publish a
1700-1800
row worksheet as an ebook complete with TOC (bookmarks) navigation,
images, and internal cross links.


On reflection I guess you are correct. I have very little knowledge
of Excel and only a little more of Crystal. Further, although I use
to be a C programmer before I retired, I know very little about VBA.
Hence, I think my comments were ill-judged. You are, of course,
absolutely right also about time. Time equals experience. I have
spent many hours trying to write a simple vba routine which I could
have written in a few minutes in C.

Kind regards

Brian


Yes, programming languages are somewhat challenging when we switch from
one to another. I started programming VBA and included VB6 within a
year. I duplicated my Excel addins as stand-alone Win apps using the
Farpoinr Spread.ocx. I tried duplicating this VB6.exe in C++ and I can
say it was a bit daunting, though I was able to succeed complete with
CHM help. Had to go MFC for the ocx to work in C++ 2008, but works nice
in VS6 C++! I'll stick to VB thank you!<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #9   Report Post  
Old February 8th 16, 07:20 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2016
Posts: 8
Default Margins and cell widths

On 08/02/2016 17:42, GS wrote:
On 08/02/2016 14:17, GS wrote:
On 07/02/2016 18:24, GS wrote:
That's great, Brian! Glad to help and I appreciate the feedback.

I know the RowHeight is font-dependant and so is the reason the value
doesn't 'jive' with ColumnWidth. For example, to set up a sheet for
creating forms I start with a graph paper layout grid. Using Arial 8pt
as my default font...

RowHt = 12.00
ColWd = 2.00

..to get square-ish cells so I can manage options as checkboxes or
choices directly on the form via Worksheet events. Naturally the
gridlines are turned off so I use the 'dotted' border style for field
input areas. My point is.., changing to a taller font or larger font
size makes the cells more rectangular vertically and so messes up the
square appearance of my bordered option cells.<g


The real point is that Excel is not a layout program. If one is
really serious about the final print layout one should use something
like Crystal Reports or possibly InDesign. I have used Crystal
Reports - I found it very difficult but it is an amazing program -
with it one can achieve the precise layout that one wants extracting
data from one or more Excel spreadsheets, correct to a fraction of a
millimetre. (By the way, Crystal Reports is very expensive!). Using
Excel for print layout will always be for the poor man, poor in time
and poor in money!

Regards

Brian

Um.., I basically disagree with your assessment of Excel as a layout
program. The precision is in pixels, and is very accurate. So unless
you're doing counterfeiting, I can't imagine why anyone would need finer
precision or another app for reporting.

As for time.., well that depends on skill level. Poor in time means
'don't have' and so suggests using your other apps is more time
consuming. On average, it takes less than 1 hour to publish a 1700-1800
row worksheet as an ebook complete with TOC (bookmarks) navigation,
images, and internal cross links.


On reflection I guess you are correct. I have very little knowledge
of Excel and only a little more of Crystal. Further, although I use
to be a C programmer before I retired, I know very little about VBA.
Hence, I think my comments were ill-judged. You are, of course,
absolutely right also about time. Time equals experience. I have
spent many hours trying to write a simple vba routine which I could
have written in a few minutes in C.

Kind regards

Brian


Yes, programming languages are somewhat challenging when we switch from
one to another. I started programming VBA and included VB6 within a
year. I duplicated my Excel addins as stand-alone Win apps using the
Farpoinr Spread.ocx. I tried duplicating this VB6.exe in C++ and I can
say it was a bit daunting, though I was able to succeed complete with
CHM help. Had to go MFC for the ocx to work in C++ 2008, but works nice
in VS6 C++! I'll stick to VB thank you!<g


Well I retired precisely 25 years ago before the pressure to learn C++
was so severe that one had to learn it. I did like C and the debugging
in VS, but having started out with MASM and DOS, I have always had a
strong feeling that they were the best even though in my heart I know
that it would be extremely difficult, if not impossible, to beat a
modern compiler and say C++ for execution speed using an assembler.
Nonetheless, the hand crafted assembler would be many times less greedy.
It would also take many more hours to write, which I prefer to forget!

Kind regards

Brian

















Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cell margins/padding ll17 Excel Discussion (Misc queries) 1 May 7th 07 11:10 PM
Cell Margins macka63 Setting up and Configuration of Excel 2 September 26th 05 07:40 AM
How to I set the margins for a cell in Excel? blinko Excel Discussion (Misc queries) 1 March 8th 05 05:51 PM
set cell margins Junebug Excel Discussion (Misc queries) 6 February 17th 05 04:52 PM
cell margins rasty_9 Setting up and Configuration of Excel 1 January 10th 05 07:26 PM


All times are GMT +1. The time now is 10:55 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017