Insert photo Problem

[Derek]

Hi all,

I am wondering how to insert any photo into a merged cell of fixed size such
that the photo will always fit in the cell. Do I need to write some code to
achieve this? Is this possible?

Thank you in advance,

Derek

 

[Dave Peterson]

Manually, you can hold the alt-key down when you resize/move the picture. It'll
snap-to the edge of the cell.

In a macro, you could do something like:

Option Explicit
Sub testme01()

Dim myPict As Picture

With ActiveSheet.Range("c1")
Set myPict = .Parent.Pictures.Insert("C:\mypict.bmp")
myPict.Top = .Top
myPict.Width = .Width
myPict.Height = .Height
myPict.Left = .Left
myPict.Placement = xlMoveAndSize
End With

End Sub

 

[Derek]

Hi Dave,

Many thanks for your advice.

As I am a newbie in VBA, I would be gratedful if you could advise how to
insert a photo dynamically into a cell instead of hardcoding a specific
file.

Again, thanks for your help.

Best regards,
Derek

 

[Dave Peterson]

Does insert a phone dynmamically mean you want to choose it from a File|Open
dialog?

If yes:

Option Explicit
Sub testme01()

Dim myPict As Picture
Dim curWks As Worksheet
Dim myPictName As Variant

Set curWks = ActiveSheet
myPictName = Application.GetOpenFilename

If myPictName = False Then
Exit Sub
End If

Set myPict = curWks.Pictures.Insert(myPictName)
With ActiveSheet.Range("c1")
myPict.Top = .Top
myPict.Width = .Width
myPict.Height = .Height
myPict.Left = .Left
myPict.Placement = xlMoveAndSize
End With

End Sub

If you know what the extension is for the file that you want to retrieve, you
could even limit the files shown:

Just make that getopenfilename look more like this:
myPictName = Application.GetOpenFilename(filefilter:="JPG Files,*.jpg")

See VBA's help for lots more details.

 

[Dave Peterson]

Phone = photo (darn fingers!)

Does insert a phone dynmamically mean you want to choose it from a File|Open
dialog?

 

[Peo Sjoblom]

Some things may change, by Dave's darn fingers won't <bg>

 

[Debra Dalgleish]

by? <vbg>

Some things may change, by Dave's darn fingers won't <bg>

 

[Peo Sjoblom]

Nor will my darn fingers either <bg>

 

[Derek]

Hi Dave,

You code works PERFECT! I am wondering how to make it work for a selected
cell instead of just C1.
Please forgive my silly question for I know nothing about VBA.

Many thanks for your help.

Best regards,
Derek

 

[Derek]

Hi TOm,

I tried ActiveSheet.Range(ActiveCell) but resulted in runtime error. Just
did not realize it was even simpler before I read your message. How stupid
am I!

THanks anyway.

Derek

 

[Derek]

Hi Dave,

Sorry to trouble you again. Your code runs perfect in Excel XP. However, it
doesn't run in Excel 97 and it states "Unable to get the insert properties
of the Pictures class". What should I install in Excel 97 so that the code
can be run?

Thanks in advance.
Derek

 

[Dave Peterson]

I don't have xl97 anymore, but I don't see anything that's unique to xl2k or
higher.

There is a bug in xl97 that was fixed in xl2k that had to do with how you ran
the code. Does the code run ok if you hit Tools|macro|macros...|Run?

And is the error a 1004 Unable to...

And the code fails when you run it from a commandbutton from the controltoolbox
toolbar?

If yes, then change the .takefocusonclick property of the commandbutton to
False.

(Or add:
activecell.activate
to the top of the code.)

If this isn't the problem, post back with what line is highlighted when it blows
up.

Hi Dave,

Sorry to trouble you again. Your code runs perfect in Excel XP. However, it
doesn't run in Excel 97 and it states "Unable to get the insert properties
of the Pictures class". What should I install in Excel 97 so that the code
can be run?

Thanks in advance.
Derek

 

[Derek]

Hi Dave,

I forgot to check the unhide object in the tools|options menu. It works fine
after i unhide object.

Cheers,
Derek