How do I convert a picture's .Width to .Column.Width so I can embed it into a cell whose .ColumnWidth must be set?

[OaklandJim]

I did research this but cannot figure it out.

I am trying to embed a picture into a cell. My problem is that I need to convert the picture's .Width to the .ColumnWidth for the cell into which the picture will be embedded, so the cell is wide enough. How do I do the conversion?

I have researched this but the best I could find is the following sub that converts a CELL's .Width to .ColumnWidth.

Source: What is the unit of Excel column width? - Simple Excel VBA

Sub f_width(rng As Range, i As Long)

Dim j As Long

With rng
    For j = 1 To 3
        .ColumnWidth = i / .Width * .ColumnWidth
    Next j
End With

End Sub

The logic works well to convert a CELL's .Width to .ColumnWidth. But I need to convert the PICTURE's .Width to .ColumnWidth for the cell into which the picture is embedded.

I hope that a math plus Excel wizard can tell me how to do this.

 

[jolivanes]

Where does the picture come from?
From a file or is it in a sheet?
Your question is to convert a picture width to a column width but by the looks of your code snippet, you want to set the column width to picture width. Or did I read it wrong?

Is this of any help? I am sure you can change it to fit your needs but if not, make sure to come back here and let us know what needs to be done.

Sub Insert_Picture()
    Dim Pict As Variant
    Dim ImgFileFormat As String
    Dim PictCell As Range
    Dim sShape As Picture
    ActiveSheet.Protect False, False, False, False, False
    ImgFileFormat = "All Picture Files(*.emf;*.wmf;*.jpg;*.jpeg;*.jfif;*.jpe;*.png;*.bpm;*.gif;*.gfa;*.emz;*.wmz;*.pcz;*.tif;*.tiff;*.cgm;*.eps;*.pct;*.pict;*.wpg;*.pcd;*.pcx;*.cdr;*.fpx;*.mix), *.bmp"
    Pict = Application.GetOpenFilename(ImgFileFormat)
    Set PictCell = Cells(10, 4)
    Set sShape = ActiveSheet.Pictures.Insert(Pict)
    With sShape.ShapeRange
         .LockAspectRatio = msoFalse
         .Top = PictCell.Top
         .Left = PictCell.Left
         .Width = Columns("D:H").Width
         .Height = Rows("10:30").Height
         .Name = "Picture 1"
     End With
End Sub

 

[OaklandJim]

The picture is in a worksheet. I want to copy it to a cell but I need to size the cell first. I know PICTURE width but cannot figure out how to size the CELL width so the picture fits into it. I need to convert PICTURE width to the width needed for the CELL.

 

[jolivanes]

Are "I know PICTURE width but cannot figure out how to size the CELL width" and "I need to convert PICTURE width to the width needed for the CELL." two different things?

 

[OaklandJim]

My picture's width is 110.436. I want to place it into a cell. How wide (.ColumnWidth) should the column be for the cell into which the picture is pasted so that the column is wide enough to accommodate the embedded picture?

 

[jolivanes]

So far the nearest I've been able to google find is this.

Sheets("Sheet1").Columns(5).ColumnWidth = ActiveSheet.Shapes("Picture 1").Width / 5.29

 

[jolivanes]

Microsoft Excel » Why you will never size Excel columns correctly » Business Brains

Have you ever tried to fit your spreadsheet columns to fit exact measurements in inches or centimetres and failed? This is how Excel measures column widths.

www.businessbrains.ie

You might need to get it as close as possible and change the width of the picture slightly to fit the column.

 

[OaklandJim]

Thanks for the inspiration. I experimented and found that dividing picture width by 5.369 gives pretty precise results.

 

[jolivanes]

Yes, I made myself a note to start at 3.5 after experimenting a little.
Mind you, I've always gone the other way, set the picture width relative to the column width.

Good luck Jim

 

[OaklandJim]

But what if the cell is too small? So you found that there are 3.5 picture .Width units per Excel .ColumnWidth units? The answer you provided -- 5.29 -- seems more correct? I experimented a fair amount to get to the 5.369.