Results 1 to 9 of 9

Column width in centimeters?

This is a discussion on Column width in centimeters? within the Excel Questions forums, part of the Question Forums category; I have a paper form that I would like to fill in by using the data entered in cells in ...

  1. #1
    Board Regular
    Join Date
    Jan 2003
    Posts
    182

    Default Column width in centimeters?

    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.

  2. #2
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416

    Default Re: Column width in centimeters?

    See:
    Q130050 - XL: Macros to Set Row Height and Column Width (in inches or
    centimeters)
    http://support.microsoft.com/support.../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

  3. #3
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416

    Default Re: Column width in centimeters?

    Further to my previous post, see these macros from http://www.erlandsendata.no/english/...owcolumnmm.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

  4. #4
    Board Regular
    Join Date
    Jan 2003
    Posts
    182

    Default Re: Column width in centimeters?

    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

  5. #5
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416

    Default Re: Column width in centimeters?

    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

  6. #6
    Board Regular
    Join Date
    Jan 2003
    Posts
    182

    Default Re: Column width in centimeters?

    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.

  7. #7
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329

    Default Re: Column width in centimeters?

    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
    Richie

  8. #8
    Board Regular
    Join Date
    Jan 2003
    Posts
    182

    Default Re: Column width in centimeters?

    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 !!

  9. #9
    New Member
    Join Date
    May 2011
    Posts
    10

    Default Re: Column width in centimeters?

    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com