resize picture to cell size

K

Kade

Active Member
Joined
Jul 20, 2002
Messages
258
hi all,

I have searched for ages and can find plenty of references to resize a cell to a picture....... but I want a picture to resize to the cells size.

I do not want to use a cell note
the size of the cells have been set at ht 225 width 58

i understand that what I want may distort the image but thats ok for my need

thanks to the board I currently use this, which is great , but only if the images imported are the same size

Sub InsertPicture()

Dim myPicture As String

myPicture = Application.GetOpenFilename _
("Pictures (*.gif; *.jpg; *.bmp; *.tif),*.gif; *.jpg; *.bmp; *.tif", , "Select Picture to Import")
If myPicture <> "" Then
ActiveSheet.Pictures.Insert (myPicture)
ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Select
Selection.ShapeRange.ScaleWidth 0.64, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.64, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.97, msoFalse, msoScaleFromTopLeft

End If
End Sub

I presume it is the last three lines I need to rejig.

tia kd
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Hello, try adapting the following:

<font face=Courier New>myPic <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>
<SPAN style="color:darkblue">Set</SPAN> myPic = ws.Pictures.Insert(MyDir & Nme)
<SPAN style="color:darkblue">With</SPAN> myPic
    .Top = ws.[a1].Top
    .Left = ws.[a1].Left
    .ShapeRange.LockAspectRatio = msoFalse
    .ShapeRange.Height = ws.[a1].RowHeight
    .ShapeRange.Width = ws.[a1].Width
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
<SPAN style="color:darkblue">Set</SPAN> myPic = <SPAN style="color:darkblue">Nothing</SPAN></FONT>

Hope this helps.

Edit: pulled a comment that didn't make sense.
 
Upvote 0
thanks Nate

Am i seeing things or did u edit your post? :confused:

this has helped a lot but I when I run it, it doesn't fill to the width (height and top left fine). Can u see any probs in what I have

btw it seems to be about a ratio of 5.5 to 6 times filling entire width, but is not consistant

tia kd


With Selection
.ShapeRange.LockAspectRatio = msoFalse
.Top = ActiveCell.Top

.Left = ActiveCell.Left

.ShapeRange.Height = ActiveCell.RowHeight
.ShapeRange.Width = ActiveCell.ColumnWidth

End With
 
Upvote 0
Hi all

with a little help from my friends and trial and error this does what I want.

Imports selected picture, and resizes the picture to what ever the cell size is.

Can anyone explain why I need to multiply by 5.25 and add 4 to make it fit width wise (it appears to work for any cell width I try)
.ShapeRange.Width = ActiveCell.ColumnWidth * 5.25 + 4

Is it something to do with the units of cell width????, or do I just accept that it works :confused:


Sub InsertPicture()

Dim myPicture As String

myPicture = Application.GetOpenFilename _
("Pictures (*.gif; *.jpg; *.bmp; *.tif),*.gif; *.jpg; *.bmp; *.tif", , "Select Picture to Import")
If myPicture <> "" Then
ActiveSheet.Pictures.Insert (myPicture)
ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Select

With Selection

.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.Height = ActiveCell.RowHeight
.ShapeRange.Width = ActiveCell.ColumnWidth * 5.25 + 4
.Placement = xlMoveAndSize


End With
End If
End Sub
 
Upvote 0
Hello, I did modify my post.

Instead of using columnwidth, use width like I did, this will return the number you want.

Oh no need to select the object, just set it on the insert and work with it from there.
 
Upvote 0
Thanks Nate, I should have read your answer more carefully , I have been trying to do that for ages it is a great assistance to me for doing reports.

kd
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Insert Image VBA
Replies
3
Views
362
Anthony47
A
S
  • Solved
VBA Code - Center/Size picture across merged cells
Replies
2
Views
301
Spaztic
S
C
  • Question
VBA Insert Picture Into Cell - Not working
Replies
7
Views
939
sancarn
sancarn
M
  • Question
Adding Logos to Workbook in Specific Locations
Replies
9
Views
555
michele227
M
melodramatic
  • Question
Vba - insert photo with varying sizes
Replies
1
Views
284
Micron
M

Forum statistics

Threads
1,215,178
Messages
6,123,484
Members
449,100
Latest member
sktz

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top