Hi,

I have the following Macro but it seems that I need the name of the
picture along with the extension in order to work, is there a way to
include the extensions (.gif, .jpg, .jpeg, and more that I don't
know ........) into the Macro so that I don't need to include the
picture name with the extension.
Thank You.

-----------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myScale As Double
If Target.Address < "$L$21" Then Exit Sub

'Select the cell where the picture is placed
Application.EnableEvents = False
On Error Resume Next
ActiveSheet.Shapes("KnownPictureName").Delete
On Error GoTo 0

Range("L10").Select
'Insert the picture
On Error GoTo NoPic

'this is the one with the link to the file that I need the extension
ActiveSheet.Pictures.Insert("C:\Temp\Pix\" &
Range("L21").Value).Select

GoTo GotPic
NoPic:
ActiveSheet.Pictures.Insert("C:\Temp\Pix\No Pic.jpg").Select
GotPic:
'scale the picture to the width of the column
myScale = 42 / Selection.ShapeRange.Height
Selection.Name = "KnownPictureName"
Selection.ShapeRange.ScaleWidth myScale, msoFalse,
msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight myScale, msoFalse,
msoScaleFromTopLeft
Range("L22").Select

Application.EnableEvents = True
End Sub
-----------------------------------------------

Just for what you are doing you don't need the name at all (though you might
want to give it a name and store it for some future process)

Dim shr As ShapeRange

'code

Set shr = activesheet.Pictures.Insert(filename).ShapeRange

now replace all "Selection.ShapeRange" with "shr"

Hmm, had anther glance at your code, not sure I quite follow what you aiming
to do overall. If the above is not enough revert back and explain.

Regards,
Peter T



"marc747" wrote in message
...
Hi,

I have the following Macro but it seems that I need the name of the
picture along with the extension in order to work, is there a way to
include the extensions (.gif, .jpg, .jpeg, and more that I don't
know ........) into the Macro so that I don't need to include the
picture name with the extension.
Thank You.

-----------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myScale As Double
If Target.Address < "$L$21" Then Exit Sub

'Select the cell where the picture is placed
Application.EnableEvents = False
On Error Resume Next
ActiveSheet.Shapes("KnownPictureName").Delete
On Error GoTo 0

Range("L10").Select
'Insert the picture
On Error GoTo NoPic

'this is the one with the link to the file that I need the extension
ActiveSheet.Pictures.Insert("C:\Temp\Pix\" &
Range("L21").Value).Select

GoTo GotPic
NoPic:
ActiveSheet.Pictures.Insert("C:\Temp\Pix\No Pic.jpg").Select
GotPic:
'scale the picture to the width of the column
myScale = 42 / Selection.ShapeRange.Height
Selection.Name = "KnownPictureName"
Selection.ShapeRange.ScaleWidth myScale, msoFalse,
msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight myScale, msoFalse,
msoScaleFromTopLeft
Range("L22").Select

Application.EnableEvents = True
End Sub
-----------------------------------------------

This fills L10 vertically with the picture.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myAspectRatio As Double
Dim myPict As Picture
Dim TestStr As String
Dim mySfx As Variant
Dim sCtr As Long
Dim myPath As String
Dim myFileName As String
Dim FoundIt As Boolean

If Target.Cells.Count 1 Then
Exit Sub
End If

If Intersect(Target, Me.Range("L21")) Is Nothing Then
Exit Sub
End If

'don't check empty cells
If Trim(Target.Value) = "" Then
Exit Sub
End If

mySfx = Array(".jpg", ".gif", ".jpeg", ".bmp")

myPath = "C:\Temp\Pix"
myPath = "U:\My Pictures\2005_01_04"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

On Error Resume Next
Me.Shapes("KnownPictureName").Delete
On Error GoTo 0

FoundIt = False
On Error Resume Next 'in case the path is really bad!
For sCtr = LBound(mySfx) To UBound(mySfx)
myFileName = myPath & Target.Value & mySfx(sCtr)
TestStr = ""
TestStr = Dir(myPath & Target.Value & mySfx(sCtr))
If TestStr = "" Then
'keep looking, it wasn't found
Else
FoundIt = True
Exit For 'stop looking
End If
Next sCtr
On Error GoTo 0

If FoundIt = False Then
'what should happen??
Exit Sub
End If

Application.ScreenUpdating = False

Set myPict = Me.Pictures.Insert(myFileName)

With myPict
.Name = "KnownPictureName"
myAspectRatio = .Width / .Height
.ShapeRange.LockAspectRatio = msoTrue
End With

With Target
myPict.Top = .Top
myPict.Left = .Left

myPict.Height = .Height
myPict.Width = myAspectRatio * .Height

If myPict.Width .Width Then
'too wide for the cell
'With the aspectratio locked, the
'reducing the width will reduce the height
myPict.Width = .Width
End If
End With

Application.ScreenUpdating = True

End Sub


marc747 wrote:

Hi,

I have the following Macro but it seems that I need the name of the
picture along with the extension in order to work, is there a way to
include the extensions (.gif, .jpg, .jpeg, and more that I don't
know ........) into the Macro so that I don't need to include the
picture name with the extension.
Thank You.

-----------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myScale As Double
If Target.Address < "$L$21" Then Exit Sub

'Select the cell where the picture is placed
Application.EnableEvents = False
On Error Resume Next
ActiveSheet.Shapes("KnownPictureName").Delete
On Error GoTo 0

Range("L10").Select
'Insert the picture
On Error GoTo NoPic

'this is the one with the link to the file that I need the extension
ActiveSheet.Pictures.Insert("C:\Temp\Pix\" &
Range("L21").Value).Select

GoTo GotPic
NoPic:
ActiveSheet.Pictures.Insert("C:\Temp\Pix\No Pic.jpg").Select
GotPic:
'scale the picture to the width of the column
myScale = 42 / Selection.ShapeRange.Height
Selection.Name = "KnownPictureName"
Selection.ShapeRange.ScaleWidth myScale, msoFalse,
msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight myScale, msoFalse,
msoScaleFromTopLeft
Range("L22").Select

Application.EnableEvents = True
End Sub
-----------------------------------------------

--

Dave Peterson