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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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 !!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top