VBA insert a photo

[azidrane]

I am inserting a photo into a worksheet and sizing it to a specific
range when a name is chosen from a list validated cell. I am using the
following code:

*********************************
Dim RepPhoto As Picture
Dim CurrentWorksheet As Worksheet
Dim RepPhotoFileName As Variant
Dim strFirstName As Variant
Dim strLastName As Variant

Set CurrentWorksheet = ActiveSheet

'Get the FIRST NAME of the full name stored in the cell H5
strFirstName = Trim(Right(Range("H5"), Len(Range("H5")) - InStr(1,
Range("H5"), ",", vbTextCompare) - 1))

''Get the LAST NAME of the full name stored in the cell H5
strLastName = Trim(Left(Range("H5"), InStr(1, Range("H5"), ",",
vbTextCompare) - 1))

'Store this as a file name
RepPhotoFileName = "\\Networkdrive\photos\" & strFirstName & " " &
strLastName & ".jpg"

'Insert the photo into the worksheet
Set RepPhoto = CurrentWorksheet.Pictures.Insert(RepPhotoFileName)

'Resize the photo to this merged cell (B4:C10)
With Range("B4:C10")
RepPhoto.Top = .Top
RepPhoto.Width = .Width
RepPhoto.Height = .Height
RepPhoto.Left = .Left
RepPhoto.Placement = xlMoveAndSize
End With

*********************************

This works, UNTIL I share the workbook....
When it shared we get this error:
"Runtime error: 1004"
"Unable to get the Insert property of the Picture Class"

Cannot figure it out...


Also, does anyone know how to delete the photo from the work sheet when
you choose a different name?

There are no other pic's on the sheet so maybe a with statement?

Cheers!

 

[Dave Peterson]

If you look at excel's help for "Features that are unavailable in shared
workbooks", you'll see that inserting pictures is something you can't do.

Maybe you could put all the picutures on a sheet (hide that sheet) and then copy
it to the location you want.

Or just put all the pictures in that location and hide all of them--then use
code to just show the one you want.

J.E. McGimpsey has a routine that hides/unhides pictures based on the value of a
cell.
http://www.mcgimpsey.com/excel/lookuppics.html

You might be able to use some of it.

 

[azidrane]

Crap. Thanks dave. The thing is we have 194 images, all quite large.
That would really bulk up the size of the file.

Thanks for the info though.

Cheers!

 

[Dave Peterson]

Sharing a workbook isn't for wimps <vbg>.

That's one reason why most people don't share workbooks too often <vvbg>.

 

[azidrane]

Shared workbooks have become quite useful in our office.