1/8 inch grid lines

[Mango]

Hi,
I'm using Office 2003. I want to make a few templates for project layouts,
including 1/8 and 1/4 inch grid lines (that don't print). Reading similar
posts I found a macro that I think makes the grid into perfect 1/4 inch
squares. The problem is I don't really know what a macro is, or how to cut
and paste it into a module (what's a module?). I downloaded a graph paper
template. It was a symmetrical grid, but not a usable size. So I tried to
hone in on 1/8 inch by just reducing the values in the column/row boxes by
the same percentage. That didn't work because the values would snap to the
nearest allowable value (no fine tune adjustments). So is there a way to make
those point values in the width/height boxes not snap to stepped values?
(i.e. use pixels instead of point values). Do I really have to figure out how
to install a macro? If so, can I make the macro conform the grid to diiferent
sizes? I know Excel is not designed to be a layout program, but jeez, a
simple symmetrical grid pattern built in as a choice on the tool options menu
is too much to ask?
-Mango

 

[MartinW]

Hi Mango,

Maybe,
Click on the box at the row and column header intersection
to select all rows and columns.
Then right click on one of the column headers select column
width and set it to 0.8.
Then right click on one of the row headers select row height
and set it to 8.

This should give you a grid of around an 1/8th of an inch
when printed on an A4 sheet (11x8)
however it is not a very usable size for a spreadsheet.

HTH
Martin

 

[Mango]

Hi Martin,
I don't understand your response unfortunately. Where's the box at the row
and column header intersection? To select all rows and columns can't I just
hit Ctrl A? What you're suggesting sounds like what I tried already by
accessing the Format menu. The values for height and width aren't measured in
the same units (points). They're both called points for some reason but they
aren't the same. A value of 11.5 for height and 1.29 for width results in a
square grid, but not the size I want.

 

[Gord Dibben]

Mango

Very difficult to get an exact 1/8th grid natively in Excel.

Row heights are measured in points or pixels. There are 72 points to an inch
and "maybe" 96 pixels to the inch.

The number that appears in the Standard column width box is the average number
of digits 0-9 of the standard font that fit in a cell.

For an interesting and enlightening discussion on this subject see

http://snipurl.com/dzz8

If you want to use VBA to set height and width in mm.

Ole Erlandson has code for setting row and column dimensions.

http://www.erlandsendata.no/english/index.php?d=envbawssetrowcol


Gord Dibben Excel MVP

 

[Bill Sharpe]

Hi,
I'm using Office 2003. I want to make a few templates for project layouts,
including 1/8 and 1/4 inch grid lines (that don't print). Reading similar
posts I found a macro that I think makes the grid into perfect 1/4 inch
squares. The problem is I don't really know what a macro is, or how to cut
and paste it into a module (what's a module?). I downloaded a graph paper
template. It was a symmetrical grid, but not a usable size. So I tried to
hone in on 1/8 inch by just reducing the values in the column/row boxes by
the same percentage. That didn't work because the values would snap to the
nearest allowable value (no fine tune adjustments). So is there a way to make
those point values in the width/height boxes not snap to stepped values?
(i.e. use pixels instead of point values). Do I really have to figure out how
to install a macro? If so, can I make the macro conform the grid to diiferent
sizes? I know Excel is not designed to be a layout program, but jeez, a
simple symmetrical grid pattern built in as a choice on the tool options menu
is too much to ask?
-Mango

I'm not sure you want to take such a drastic step to solve the problem,
but Quattro Pro and OpenOffice.Org Calc let you specify row height and
column width in inches, if desired. The latter is free.

Bill

 

[Mango]

Gord and Bill,
Thank you for your responses. I did read another thread on this subject, not
sure if it's the one you're alluding to Gord, and there was a site I found
where someone had written some code for a macro that I believe made a 1/4
inch grid. The problem was I didn't understand the instructions to be able to
use it. I have never made a macro before. Bill, Are those programs you
suggested or just other macros? Or are macros little programs themselves? I
don't know, it seems to be getting a little too complicated. I guess I'll
just use the graph paper grid I can download and make my drawings on that.
The fact that it's square means it will still be to scale, I just won't be
able to lay a ruler on the plan to measure off any intermediate distances.
I'm just making little plan drawings of simple remodeling projects for
clients (without the gridlines printing out). I guess Excel's not really
meant for drawing on a symmetrical grid.I should probably invest in some
architectural software.

 

[Gord Dibben]

Mango

First off. I tried Martin's advice and looks very good to me with a tiny
adjustment.

Row height to 9 and column width to 0.9 looks pretty close to 1/8th inch to my
eyes.

As far as macros go..............................

I'm assuming you did not go to Ole Erlandson's site where you would find code to
set heights and widths in mm.

3.175mm = 1/8th inch

I will post the necessary code here below for you and anyone else interested in
this subject.

Sub SetColumnWidthMM(ColNo As Long, mmWidth As Integer)
' changes the column width to mmWidth
Dim w As Single
If ColNo < 1 Or ColNo > 255 Then Exit Sub
Application.ScreenUpdating = False
w = Application.CentimetersToPoints(mmWidth / 10)
While Columns(ColNo + 1).Left - Columns(ColNo).Left - 0.1 > w
Columns(ColNo).ColumnWidth = Columns(ColNo).ColumnWidth - 0.1
Wend
While Columns(ColNo + 1).Left - Columns(ColNo).Left + 0.1 < w
Columns(ColNo).ColumnWidth = Columns(ColNo).ColumnWidth + 0.1
Wend
End Sub

Sub SetRowHeightMM(RowNo As Long, mmHeight As Integer)
' changes the row height to mmHeight
If RowNo < 1 Or RowNo > 65536 Then Exit Sub
Rows(RowNo).RowHeight = Application.CentimetersToPoints(mmHeight / 10)
End Sub

This example macro shows how you can set the row heights to 3.175mm and the
column widths 3.175mm.

Sub ChangeWidthAndHeight()
Dim w As Long
Dim r As Long
For w = 1 To 60
SetColumnWidthMM w, 3.175
Next w
For r = 1 To 120
SetRowHeightMM r, 3.175
Next r
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

I don't use 2007 yet but I'll give you the 2003 and earlier instructions.

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the three sets of code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the ChangeWidthAndHeight macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.

NOTE: the code operates on whatever sheet is selected before running the macro.


Gord

 

[MartinW]

Hi Mango,

Now that Gord has sorted out your grid size, I really think you are
on the wrong track here. You're trying to chop down a tree with a
shovel! It can be done, but takes a lot of sweat and a lot of frustration.

You should be looking at CAD software. I use Autocad which is by
far the best however, at over $5,000, is only for corporate users and
consultant designers and the like. There are plenty of free or relatively
cheap programs available on the net and I have read some good reports
on some of these programs, unfortunately I only read these posts
with a passing interest and can't remember the details just now.

For advice on the best way to go do a google search for Cad discussion
groups. Most of the groups are about Autocad but provided you go to the
independent sites the people don't mind discussing other products. Steer
away from the Autodesk sites they do frown at the mention of any other
product (Autodesk is the Microsoft of the CAD world)

Believe me, once you have drawn something in CAD you will never
attempt to draw anything more than a line or a box in Excel or Word
or the like. Even for fundamental shapes you get a much crisper image
by drawing it in CAD and exporting to Excel, Word etc. as CAD is
vector based so that a curve is a curve and not a pixellised
representation of a curve.

HTH
Martin

 

[Bill Sharpe]

Gord and Bill,
Thank you for your responses. I did read another thread on this subject, not
sure if it's the one you're alluding to Gord, and there was a site I found
where someone had written some code for a macro that I believe made a 1/4
inch grid. The problem was I didn't understand the instructions to be able to
use it. I have never made a macro before. Bill, Are those programs you
suggested or just other macros? Or are macros little programs themselves? I
don't know, it seems to be getting a little too complicated. I guess I'll
just use the graph paper grid I can download and make my drawings on that.
The fact that it's square means it will still be to scale, I just won't be
able to lay a ruler on the plan to measure off any intermediate distances.
I'm just making little plan drawings of simple remodeling projects for
clients (without the gridlines printing out). I guess Excel's not really
meant for drawing on a symmetrical grid.I should probably invest in some
architectural software.

:

Quattro Pro is the spreadsheet program in Corel's WordPerfect suite and
is available for purchase. It's cheaper than MS Office.

OpenOffice.Org Calc is the spreadsheet program in this open source suite
of programs. It's free, but it's a 100 mb download and the full
installation takes 285 mb in its program files folder. The suite also
includes a word processor, a drawing program, a database program, a
presentation program, and a math editor.

As I said, it's a drastic step to switch your spreadsheet program like
this, but it's quite easy to set up any height and width desired for a cell.

You might also consider setting up a table in Word. You can specify
column width and row height in inches in Table Properties.

Bill

 

[Mango]

Thank you Martin, Gord, and Bill for your salient advice.