Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell margins/padding | Excel Discussion (Misc queries) | |||
Cell Margins | Setting up and Configuration of Excel | |||
How to I set the margins for a cell in Excel? | Excel Discussion (Misc queries) | |||
set cell margins | Excel Discussion (Misc queries) | |||
cell margins | Setting up and Configuration of Excel |