PDA

View Full Version : Solved: Picture Code



Ismael
01-17-2006, 08:23 AM
Hi to all in the forum,

I have seen this code in EXCELTIP.

Sub TestInsertPictureInRange()

InsertPictureInRange "C:\Documents and Settings\ibpacheco\Desktop\Ficheiros Tempor?rios\Pessoais\daniela.jpg", _

Range("J4:L11")

End Sub



Sub InsertPictureInRange(PictureFileName As String, TargetCells As Range)

' inserts a picture and resizes it to fit the TargetCells range

Dim p As Object, t As Double, l As Double, w As Double, h As Double

If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub

If Dir(PictureFileName) = "" Then Exit Sub

' import picture

Set p = ActiveSheet.Pictures.Insert(PictureFileName)

' determine positions

With TargetCells

t = .Top

l = .Left

w = .Offset(0, .Columns.Count).Left - .Left

h = .Offset(.Rows.Count, 0).Top - .Top

End With

' position picture

With p

.Top = t

.Left = l

.Width = w

.Height = h

End With

Set p = Nothing

End Sub

And the code works ok, but I would like to make 1 change that is:

Instead of put the directory of the picture in the code, put something like this

Sub TestInsertPictureInRange()
InsertPictureInRange = inputbox ("Introduza O caminho completo da foto") _
Range("J4:L11")
End Sub

in order to, when we run the macro a message appear asking us for the directory.

But the code that I have try doesn't work.
Another thing is, I have create a button to put this code inside and the run the macro when I press the button, but without success.

So can anyone help me with this?

Best regards,

Ismael

Ismael
01-17-2006, 09:23 AM
If I use this:

Sub TestInsertPictureInRange()
Application.Dialogs(xlDialogInsertPicture).Show
End Sub

instead of this:

Sub TestInsertPictureInRange()
InsertPictureInRange "C:\Documents and Settings\ibpacheco\Desktop\Ficheiros Tempor?rios\Pessoais\daniela.jpg", _
Range("J4:L11")
End Sub

the problem of select the picture instead of put the directory on the code is solve, the problem is that if I do that the picture doesn't resize the range J4:L11.

Can any of you guys give me a help on this?

best regards,

Ismael

gsouza
01-17-2006, 01:17 PM
I was playing around with it but could only make it work using a userform. I attached a file.

gsouza
01-17-2006, 01:23 PM
I don't think that zip file worked, here it is again

Ismael
01-17-2006, 02:02 PM
Hi Souza,

It's perfect, very nice.

I don't imagine the time that I spend to find a solution (without success)

Thank you very much.

Best regards,

Ismael

gsouza
01-17-2006, 02:09 PM
Your welcome, it was not alot of work really, it was something I really liked and will use it at my work. I should say thank you as well. : )