Size Picture after Insert

  • Thread starter Thread starter sspittell
  • Start date Start date
S

sspittell

I am using the following macro to allow the user to insert a pictur
into any a cell.

Sub Insert_Pic()
'
' Insert_Pic Macro
' Macro recorded 10/12/2004 by INSTALL
'
' Keyboard Shortcut: Ctrl+i
'
Application.Dialogs(xlDialogInsertPicture).Show

End Sub

How do I automatically size the picture.

I have tried selection.shaperange.height =

But nothing happens, and I do not get an error
 
One way is to check that the user didn't cancel and then find the newest
picture.

Option Explicit
Sub testme()

Dim myPict As Picture
Dim resp As Boolean

resp = Application.Dialogs(xlDialogInsertPicture).Show
If resp = True Then
With ActiveSheet
Set myPict = .Pictures(.Pictures.Count)
With .Range("a1:b3")
myPict.Top = .Top
myPict.Left = .Left
myPict.Width = .Width
myPict.Height = .Height
End With
End With
Else
'user hit cancel!
End If

End Sub

Another way is to have a little more control and ask for the name of the picture
to insert:

Option Explicit
Sub testme()

Dim myPict As Picture
Dim myFileName As Variant
Dim picName As String
Dim myRng As Range

myFileName = Application.GetOpenFilename _
(filefilter:="Pictures , *.jpg;*.bmp;*.gif", Title:="Select Picture")
If myFileName = False Then
Exit Sub 'no file selected
End If

With ActiveSheet
Set myRng = .Range("a1:b3")
Set myPict = .Pictures.Insert(myFileName)
With myPict
.Left = myRng.Left
.Top = myRng.Top
.Height = myRng.Height
.Width = myRng.Width
End With
End With

End Sub

(I'd use the second version--it looks safer to me.)
 
Back
Top