Set Excel columnwidth in Centimeters

[jackni]

Hello, everyone,

In range("a1"), if you input 3,I want the column("A:A").columnwidth changes to 3 centermeters, in you input 0.5, I want the columnwidth changes to 0.5 centermeters.

Can someone here write the VBA code for me to make this happen, Thank you !!!

(Different DPI can affect the column widths.)

 

[Kamolga]

Re: Set Excel columnwidth in Centermeters

Hi,

In page layout view (the second incon near the zoom bottom right), the column width is automatically set up in cm (so when you input 0,5, the width is 0,5 cm)

 

[jackni]

Re: Set Excel columnwidth in Centermeters

Kamolga,

Thank you for your reply, I know in layout view, I can read the columnwidth in centermeters, sorry I'm not clear before, I want Excel automatically change columnwidth to row(1) number in VBA code.
In layout view, Excel cannot automatically change it's columnwidth, you can easily change columnwidth in manual, cause you can see directly columnwidth in centermeters, while in normal view, columnwidth in dots/pixel.

 

[mikerickson]

Re: Set Excel columnwidth in Centermeters

The Excel>Preferences>General tab has a dropdown where the default units can be set.

 

[Jaafar Tribak]

Re: Set Excel columnwidth in Centermeters

Hello, everyone,

In range("a1"), if you input 3,I want the column("A:A").columnwidth changes to 3 centermeters, in you input 0.5, I want the columnwidth changes to 0.5 centermeters.

Can someone here write the VBA code for me to make this happen, Thank you !!!

(Different DPI can affect the column widths.)

For a screen resolution of 1280 horizontal the following code might be close:

Private Sub Worksheet_Change(ByVal Target As Range)[COLOR=#008000]    'SCREEN RESOLUTION 1280 Horiz[/COLOR]
    If Target.Address = Range("A1").Address Then
        If VarType(Target.Value) = vbDouble Then
            Target.ColumnWidth = Target.Value * 5.4 * ((100 / ActiveWindow.Zoom))
        End If
    End If
End Sub

the above code should work more accurately for sheet zooms of 100 or close to it.

I have been experimenting with some API workarounds for a more accurate result but I haven't succeeded.

 

[Zenwood]

Re: Set Excel columnwidth in Centermeters

I don't think you can reprogram Excel at that level.

All I have found is using an approximation to get what looks like a square centimeter. (4.71 wide and 28.5 tall on a 1080p)

So my column A world be 3 centimeters wide if the width is set to 14.13

 

[jackni]

Re: Set Excel columnwidth in Centermeters

Jaafar Tribak ，

Thak you for your reply! You always help me.
Where is 5.4 come from? How to write code in different screen resolution?
The following code can read user's screen resolution.

Code:
Declare Function GetSystemMetrics32 Lib "user32" Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long
Sub DisplayMonitorInfo()
Dim X As Long, Y As Long
X = GetSystemMetrics32(0)
Y = GetSystemMetrics32(1)
MsgBox "SR：" & X & " × " & Y & "
End Sub

In my sheet , users can input numbers(in centermeters) in rows(1), for exampele, range(A1)=3, range(B1)=2,range(C1)=4, in range(A2:C4), it's a 3*3 table, I can use the rows(1)'s number to draw a table, and the table's columnwidth is 3,2,4 centermeters,I use the input numbers as columnwidth,and the numbers is measured in centermeters, NOT in pixel or dots, Because people measure table width in centermeters not in pixel or dots.
Is there a way Excel can do this ? I need the accurate measurement.
Thank you very much!

 

[Jaafar Tribak]

Re: Set Excel columnwidth in Centermeters

Jaafar Tribak ，

Thak you for your reply! You always help me.
Where is 5.4 come from? How to write code in different screen resolution?
The following code can read user's screen resolution.

Code:
Declare Function GetSystemMetrics32 Lib "user32" Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long
Sub DisplayMonitorInfo()
Dim X As Long, Y As Long
X = GetSystemMetrics32(0)
Y = GetSystemMetrics32(1)
MsgBox "SR：" & X & " × " & Y & "
End Sub

In my sheet , users can input numbers(in centermeters) in rows(1), for exampele, range(A1)=3, range(B1)=2,range(C1)=4, in range(A2:C4), it's a 3*3 table, I can use the rows(1)'s number to draw a table, and the table's columnwidth is 3,2,4 centermeters,I use the input numbers as columnwidth,and the numbers is measured in centermeters, NOT in pixel or dots, Because people measure table width in centermeters not in pixel or dots.
Is there a way Excel can do this ? I need the accurate measurement.
Thank you very much!

The 5.4 value comes from the Page Layout view ... In Page Layout, I Manually resized the Columns to 1 CM which corresponds to approximatelly to 5.4 Points on Normal View .

Obviously, this value would change depending on the current (zoom) and screen resolution which you can easily read with the GetSystemMetrics(0/1) as you pointed out...

This value gave me pretty accrurate results although NOT 100 percent accurate .

Try experimenting a bit and see if you get an accurately enough result.

Sorry, I have searched online and experimented with some API methods but I haven't been able to arrive at an accurate measurement.

 

[jackni]

Re: Set Excel columnwidth in Centermeters

Jaafar Tribak,

Anyway, Thank you very much! You already help me a lot.Thanks for your time and patience.
Thanks for mikerickson and Zenwood too.