VBA to display image on a form - image is defined in Range Name

I have a picture stored in a range name in an excel spreadsheet. I need a
VBA to display the logo on a form. How can I go about doing it? In the
form, do I define it as an image? If so, then how do I point the image to
the range name?

Sub InsertPicture(PicturePath as String, PlacementCell as Range)
' PicturePath will be the file path (from your range)
' PlacementCell is the cell where the top left of the picture will go

Dim NewPic As Object

Set NewPic = PlacementCell.Parent.Pictures.Insert(PicturePath)
NewPic.Name = "Logo" ' allows you to easily refer to the picture in the
future
' (i.e. ActiveSheet.Pictures("Logo"))
NewPic.Top = PlacementCell.Top
NewPic.Left = PlacementCell.Left

End Sub
--
- K Dales


"Tan" wrote:

> I have a picture stored in a range name in an excel spreadsheet. I need a
> VBA to display the logo on a form. How can I go about doing it? In the
> form, do I define it as an image? If so, then how do I point the image to
> the range name?

Dales

The picture is actually in the spreadsheet, and has a range name defined
with it. I would not want to store the picture in a separate file. Will
your code works to access to picture from based on the range name?

Regards
"K Dales" wrote:

> Sub InsertPicture(PicturePath as String, PlacementCell as Range)
> ' PicturePath will be the file path (from your range)
> ' PlacementCell is the cell where the top left of the picture will go
>
> Dim NewPic As Object
>
> Set NewPic = PlacementCell.Parent.Pictures.Insert(PicturePath)
> NewPic.Name = "Logo" ' allows you to easily refer to the picture in the
> future
> ' (i.e. ActiveSheet.Pictures("Logo"))
> NewPic.Top = PlacementCell.Top
> NewPic.Left = PlacementCell.Left
>
> End Sub
> --
> - K Dales
>
>
> "Tan" wrote:
>
> > I have a picture stored in a range name in an excel spreadsheet. I need a
> > VBA to display the logo on a form. How can I go about doing it? In the
> > form, do I define it as an image? If so, then how do I point the image to
> > the range name?

My apologies, Tan - I misinterpreted your post to mean the file path was
stored in the range, not the actual picture. You can do the reverse - find
the reference to the picture object, by code like this:

Public Function PictureInRange(SearchRange As Range) As Object
Dim FoundPic As Object

For Each FoundPic In SearchRange.Parent.Pictures
If Not Intersect(FoundPic.TopLeftCell, SearchRange) Is Nothing Then _
Set PictureInRange = FoundPic
Next FoundPic

End Function

However, making use of the picture might be difficult. It can be copied and
pasted elsewhere on a worksheet, but I don't know any easy way to get it onto
a form (as far as I know you cannot paste into a userform image control.
--
- K Dales


"Tan" wrote:

> Dales
>
> The picture is actually in the spreadsheet, and has a range name defined
> with it. I would not want to store the picture in a separate file. Will
> your code works to access to picture from based on the range name?
>
> Regards
> "K Dales" wrote:
>
> > Sub InsertPicture(PicturePath as String, PlacementCell as Range)
> > ' PicturePath will be the file path (from your range)
> > ' PlacementCell is the cell where the top left of the picture will go
> >
> > Dim NewPic As Object
> >
> > Set NewPic = PlacementCell.Parent.Pictures.Insert(PicturePath)
> > NewPic.Name = "Logo" ' allows you to easily refer to the picture in the
> > future
> > ' (i.e. ActiveSheet.Pictures("Logo"))
> > NewPic.Top = PlacementCell.Top
> > NewPic.Left = PlacementCell.Left
> >
> > End Sub
> > --
> > - K Dales
> >
> >
> > "Tan" wrote:
> >
> > > I have a picture stored in a range name in an excel spreadsheet. I need a
> > > VBA to display the logo on a form. How can I go about doing it? In the
> > > form, do I define it as an image? If so, then how do I point the image to
> > > the range name?

Check out Stephen Bullen's PastePicture example.
http://www.oaltd.co.uk/Excel/SBXLPage.asp#VBA

Cheers
Andy

Tan wrote:
> Dales
>
> The picture is actually in the spreadsheet, and has a range name defined
> with it. I would not want to store the picture in a separate file. Will
> your code works to access to picture from based on the range name?
>
> Regards
> "K Dales" wrote:
>
>
>>Sub InsertPicture(PicturePath as String, PlacementCell as Range)
>>' PicturePath will be the file path (from your range)
>>' PlacementCell is the cell where the top left of the picture will go
>>
>>Dim NewPic As Object
>>
>> Set NewPic = PlacementCell.Parent.Pictures.Insert(PicturePath)
>> NewPic.Name = "Logo" ' allows you to easily refer to the picture in the
>>future
>> ' (i.e. ActiveSheet.Pictures("Logo"))
>> NewPic.Top = PlacementCell.Top
>> NewPic.Left = PlacementCell.Left
>>
>>End Sub
>>--
>>- K Dales
>>
>>
>>"Tan" wrote:
>>
>>
>>>I have a picture stored in a range name in an excel spreadsheet. I need a
>>>VBA to display the logo on a form. How can I go about doing it? In the
>>>form, do I define it as an image? If so, then how do I point the image to
>>>the range name?

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info