• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Column widths change when excel file is opened on another computer

Daics

New Member
Good morning,
I have an excel worksheet which I format to a column width of 2.86 (not sure what the unit is).
A variety of data is ultimately displayed on this worksheet.
This worksheet is then provided for other staff to use.
However, when this file is opened on some other other computer(s) the column widths change (e.g. from 2.86 to 3).
The professional "look" when the worksheet is viewed on this/these other compters is negatively compromised.
For example, any shapes inserted by me on the original worksheet appear to the left of their intended position(s).

Is there a solution for this issue?

my code for formatting the worksheet
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
<<< This thread has moved to VBA Macros >>>
>>> use code - tags <<<
Code:
'FORMAT PAGES
Dim H As Worksheet
Set H = Worksheets("HOME")
H.Activate
H.Cells.RowHeight = 9.75
H.Cells.ColumnWidth = 2.86
H.Cells.Font.Name = "Arial"
H.Cells.Font.Size = 11
ActiveWindow.Zoom = 120
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1

my code for inserting a shape
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
sh_lft = 5 * 18.75
sh_tp = 10 * 9.75
sh_len = 6 * 18.75
sh_ht = 2 * 9.75
Set omnu = ActiveSheet.Shapes.AddShape(msoShapeRectangle, sh_lft, sh_tp, sh_len, sh_ht)
With omnu
            .Name = "mnu"
            .OnAction = sb_rout
            .Fill.ForeColor.RGB = mn_5_clr
            .TextFrame2.TextRange.Font.Fill.ForeColor.RGB = mn_clr
            .TextFrame2.TextRange.Characters.Font.Name = "Arial"
            .TextFrame2.TextRange.Characters.Font.Size = 9
            .TextFrame2.VerticalAnchor = msoAnchorMiddle
            .TextFrame2.TextRange.ParagraphFormat.Alignment = msoAlignCenter
            .TextFrame2.TextRange.Characters.Text = "any type of text"
            .Line.Visible = msoFalse
            .Shadow.Visible = msoFalse
            .TextFrame2.TextRange.Font.UnderlineStyle = msoUnderlineSingleLine
End With
 
Last edited by a moderator:
I think the unit Excel uses in this statement is a sort of standardized character width; since this varies with the font, it's not very exact. I'm not sure, but isn't it possible that on a different computer the user has a different default font? Maybe that's what's causing it to change the width. Easily checked, if that other machine is near your desk; just compare the font size / family / etc on that machine and yours.

That's just a guess, though. One solution, I would think, is to set the font explicitly, so that it won't change when the workbook is opened on another machine. Or you could try setting the column width in points instead of the character width.

To quote the documentation: "One unit of column width is equal to the width of one character in the Normal style. For proportional fonts, the width of the character 0 (zero) is used....Use the Width property to return the width of a column in points."
 
Thank you for this.
As a result of your suggestion I have now developed a bit of a work around.
My work around uses the left function. The code below shows how I now use a column width "unit" derived from the screen in use. This seems to work (so far it seems) on the screens of other users.
Kind regards.
>>> You've noted that use code - tags <<<
Dim unt As Double
unt = H.Cells(1, 2).Left)
sh_lft = 38 * unt
sh_tp = 5 * 9.75 + 1
sh_len = 4 * unt
sh_ht = 1.7 * 9.75
Set oshp = ActiveSheet.Shapes.AddShape(msoShapeRectangle, sh_lft, sh_tp, sh_len, sh_ht)
With oshp
.Name = "reload_tgts"
.OnAction = "MENU_1_REPORT_1_1_SUB"
.Fill.ForeColor.RGB = SU.Range("B15").Interior.Color 'dta_2_clr
.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = mn_clr
.TextFrame2.TextRange.Characters.Font.Name = "Arial"
.TextFrame2.TextRange.Characters.Font.Size = 8
.TextFrame2.VerticalAnchor = msoAnchorMiddle
.TextFrame2.TextRange.ParagraphFormat.Alignment = msoAlignCenter
.TextFrame2.TextRange.Characters.Text = "TARGETS"
.Line.Visible = msoFalse
.Shadow.Visible = msoFalse
.TextFrame2.TextRange.Font.UnderlineStyle = msoUnderlineSingleLine
End With
 
Last edited by a moderator:
Back
Top