VBA insert a photo

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!

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 wrote:
>
> 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

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!

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

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

azidrane wrote:
>
> 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

Shared workbooks have become quite useful in our office.