Column width in centimeters?

Zorax

Board Regular
Joined
Jan 1, 2003
Messages
182
I have a paper form that I would like to fill in by using the data entered in cells in Excel and then printing on to the form.

To get the form to match the Excel cells/columns, I need to be able to specify the column width in Centimeters and not the default pixels that Excel displays.

Is this possible, or am I trying something that isn't possible?

Thanks in advance.
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
See:
Q130050 - XL: Macros to Set Row Height and Column Width (in inches or
centimeters)
http://support.microsoft.com/support/kb/articles/Q130/0/50.ASP


In the above article, scroll down to see a macro that allows you to specify the row and column widths in centimeters.

Column widths are not really measured in any "unit" as such. Instead, the number refers to the number of characters which can be displayed in the column. For variable width fonts such as Arial, the "0" character is used. In VBA, the ColumnWidth property uses this measure of width, and the Width property uses Points.

This may be useful to you:

1 point = 1/72 inches.
1 inch = 72 points
! point = 0.035 centimeters (or .35 millimetre)

Regards,

Mike
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
Further to my previous post, see these macros from http://www.erlandsendata.no/english/vba/ws/setrowcolumnmm.htm

1. Set row height and column width in millimeters
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

2. This example macro shows how you can set the row height for row 3 and the column width for column C to 3.5 cm:
Sub ChangeWidthAndHeight()
SetColumnWidthMM 3, 35
SetRowHeightMM 3, 35
End Sub

HTH

Mike
 

Zorax

Board Regular
Joined
Jan 1, 2003
Messages
182
Mike

Thanks very much. I'm not having a good day today !!

Do I need to run the first macro and then the second? I've tried both but can't seem to get it to work.

My requirements are for width only:

SetColumnWidthMM 1, 30
SetColumnWidthMM 2, 30
SetColumnWidthMM 3, 109
SetColumnWidthMM 4, 23
SetColumnWidthMM 5, 19
SetColumnWidthMM 6, 19
SetColumnWidthMM 7, 22
SetColumnWidthMM 8, 22
SetColumnWidthMM 9, 18
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
Zorax,

Sorry for the delay in getting back to you (it’s now 12.45am Sunday morning my time).

These are the macros you need. They work perfectly on my machine (Excel XP). Put both macros in a standard module.

Code:
Sub ChangeWidthAndHeight()
    SetColumnWidthMM 1, 30
    SetColumnWidthMM 2, 30
    SetColumnWidthMM 3, 109
    SetColumnWidthMM 4, 23
    SetColumnWidthMM 5, 19
    SetColumnWidthMM 6, 19
    SetColumnWidthMM 7, 22
    SetColumnWidthMM 8, 22
    SetColumnWidthMM 9, 18

    End Sub
Code:
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
Note that the first macro “ChangeWidthAndHeight” calls the second macro – only the first macro will show in the macro dialog box (Tools menu | Macro | Macro – click the macro and then click Run).

I should mention that “CentimetersToPoints” is a VBA application (see the second macro). From the VBE help file – to set the left margin of Sheet1 to 5 centimeters:
Code:
Worksheets("Sheet1").PageSetup.LeftMargin = _
        Application.CentimetersToPoints(5)
Regards,

Mike
 

Zorax

Board Regular
Joined
Jan 1, 2003
Messages
182
Mike

Thank you, it works perfectly. As a beginner, there are bits of this I don't understand (like where is the call procedure in the first macro and why doesn't the second macro show in the dialog box) to name just a couple of things!

Anyway, I'll keep looking at it and perhaps it'll make sense eventually.

Thanks again.
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi Zorax,

To help you understand what is happening consider the two simple routines below.
Code:
Sub test()
    Dim strMyString As String
    'declare a variable of String data type
    strMyString = "Hello"
    'add a value to that variable
    DisplayString strMyString
    'pass that value as an argument to another routine
End Sub

Sub DisplayString(strAString As String)
    'a routine that takes a String data type as an argument
    MsgBox strAString
    'and then uses that value
End Sub
Now, the first routine is calling the second with the line "DisplayString strMyString". This calls the routine (DisplayString) and passes an argument (strMyString) to that routine. The second routine then uses that argument. Note that an alternative way of calling the routine would be "Call DisplayString(strMyString)", which is perhaps a little more self-explanatory.

As for the routines showing in the list of available macros - the first will and the second will not. The reason, as you may now have guessed, is that the first is run without any arguments and so may simply be executed. The second will only run with the necessary argument passed to it and so will not be displayed in the list.

The same basis applies to the routines that you have been given.

HTH
 

Zorax

Board Regular
Joined
Jan 1, 2003
Messages
182
Thanks for that Richie, now I understand. In my ignorance I always thought that you had to use the call command. I also now understand why the second routine wouldn't show in the dialog box.

Thanks again, I'm learning !!
 

gnulab

New Member
Joined
May 7, 2011
Messages
13
This is a really old thread, but I just stumbled upon this when I was trying to adjust my column width and row height in cm.

I'm using Excel 2010. Under Excel 2010, there is another view mode, the page layout mode. In that mode, everything is in cm, as long as you default the measurement unit into cm.

So, instead of via VB, you can do it via page layout mode.

HTH
Henry
 

Watch MrExcel Video

Forum statistics

Threads
1,095,786
Messages
5,446,490
Members
405,404
Latest member
clead

This Week's Hot Topics

Top