Size Picture to Cell

[Greg Lovern]

Is there a good way to size a picture to a cell?

Sizing the cell's width to the picture is no problem, and moving the
picture to the cell's location is no problem, but ColumnWidth is not
in points, and the Range or Column object's Width property, which is
in points, is read-only.

The question has been asked before, but...

I tried entering a "0" in a cell and autofitting it, since ColumnWidth
is based on the width of a single "0" in the normal font. But when I
check the ColumnWidth property, it wasn't exactly 1 as expected.
Instead, it was 1.29. Two zeroes ("00") had a ColumnWidth of 2.29.

Is the addition of the 0.29 to the 1 consistent, or does it depend on
various unpredictable factors?


Is there a better way of doing it than this:

1) Try changing the ColumnWidth property by increasingly larger
amounts until the minimum amount is found that actually changes the
width, because just trying to change it by 0.01 doesn't have any
effect. In a quick test just now, the minimum amount was 0.08, which
caused a change of 0.14, but obviously that depends on the normal font
and maybe other factors too.
2) Repeatedly change the ColumnWidth property by that minimum amount,
checking the resulting Width each time, until the desired Width is
reached.

Is there a better way?


Thanks,

Greg

 

[Greg Lovern]

Sizing the cell's width to the picture is no problem

Sorry, I meant to say:

Sizing the cell's HEIGHT to the picture is no problem



Greg

 

[Peter T]

You could try this little macro, start by selecting your picture

Sub ObjToCell()
With Selection
.Left = .TopLeftCell.Left
.Top = .TopLeftCell.Top
.Width = .TopLeftCell.Width
.Height = .TopLeftCell.Height
.Placement = xlMoveAndSize
End With
End Sub

Post back if you need to process loads on a sheet.

Manually, customize your Drawing toolbar and add the 'Snap to grid' button'.
You'll quickly figure how to size it to cell(s).

Regards,
Peter T

 

[Greg Lovern]

You could try this little macro, start by selecting your picture

Sub ObjToCell()
With Selection
.Left = .TopLeftCell.Left
.Top = .TopLeftCell.Top
.Width = .TopLeftCell.Width
.Height = .TopLeftCell.Height
.Placement = xlMoveAndSize
End With
End Sub

Thanks, but I see now that I asked the wrong question. I don't want to
size the picture to the cell. Instead, I want to size the cell to the
picture.

I'll post the question in a new thread.


Thanks,

Greg

 

[Sam Kuo]

Hi Peter

I was also looking for this code, but with slight complication..

Instead of sizing the picture to a single cell, I need to size it to fit
within a merged cell B10:AK30. Is this possible and how?

Many thanks
Sam

 

[Peter T]

Sub ObjToCells()
Dim rng As Range
Dim shp As Shape

On Error Resume Next
Set shp = ActiveSheet.Shapes(Selection.Name)
If shp Is Nothing Then
MsgBox "start by selecting the Shape to be sized"
Exit Sub
End If
On Error GoTo 0

' Set rng = ActiveSheet.Range("B10:AK30")
' or
Set rng = ActiveSheet.Range("B10").MergeArea

With rng
shp.Left = .Left
shp.Top = .Top
shp.Width = .Width
shp.Height = .Height
End With

End Sub

You can set the range to size to either way, which may depend on needs or be
equivalent --

set rng = ActiveSheet.Range("B10:AK30")
will size to B10:AK30, irrespective as to whether cells are merged

set rng = ActiveSheet.Range("B10").MergeArea
will size to any merged area that includes B10, or just the single cell if
not merged.

Regards,
Peter T

 

[Sam Kuo]

Thanks Pete. Your explanation is also very helpful.

Sam