CODEWITHSUNDEEP
excelvbaimage
franciscofcosta
franciscofcosta

Reputation: 843

Resizing picture in Excel VBA

I have got a VBA script that will fetch pictures and stick them into a specific cell in Excel, depending on another cell's value.

Public Function PictureLookup(Value As String, Location As Range, Index As Integer)

Application.Volatile

Dim lookupPicture As Shape
Dim sheetName As String
Dim picTop As Double
Dim picLeft As Double

sheetName = Location.Parent.Name

'Delete current picture with the same Index if exists
For Each lookupPicture In Sheets(sheetName).Shapes
    If lookupPicture.Name = "PictureLookup" & Index Then
        lookupPicture.Delete
    End If
Next lookupPicture

'Get position of cell calling the UDF
picTop = Location.Top
picLeft = Location.Left

'Add the picture in the right location
Set lookupPicture = Sheets(sheetName).Shapes.AddPicture _
("G:\My Drive\MY FOLDER\LOGOS\" & Value & ".jpg", msoFalse, msoTrue, picLeft, picTop, -1, -1)

'change the picture name
lookupPicture.Name = "PictureLookup" & Index

PictureLookup = ""

End Function

At the moment, the pictures are inserted into Excel in their original size. I would like for all of them to have the exact size of 0.38x0.38. I can't seem to figure out which properties to change in the script so that that can happen.

Thank you

Upvotes: 1

Views: 5275

Answers (1)

Vityata
Vityata

Reputation: 43565

lookupPicture.Height = 38
lookupPicture.Width = 38

This is how to Scale the properties Width and Height:

lookupPicture.ScaleWidth 10, msoFalse, msoScaleFromTopLeft
lookupPicture.ScaleHeight 10, msoFalse, msoScaleFromTopLeft

MSDN Shape.ScaleWidth Method

Scales the width of the shape by a specified factor. For pictures and OLE objects, you can indicate whether you want to scale the shape relative to the original or the current size. Shapes other than pictures and OLE objects are always scaled relative to their current width.

Upvotes: 3

Related Questions