Not so picture perfect

[Lukas]

I am a newby at this, actually I am a frustrated newby. I just can’t get a handle on this comment problem.
I have a folder (Pictures) that contains numerous jpeg images. All the images are sequentially numbered; let’s say 1 through 58. I want to attach these pictures as comments on a worksheet. Specifically in column F. The cells in column F are number coded such as 1,7,8,7,7,5,3, etc in F1, F2, F3, F4, F5, F6, F7 etc respectively. Only those cells containing the number 7 need a picture from the picture folder as a comment. So that picture 1 would be in cell F2, picture 2 would be in F4, picture 3 would be in F5 and so on until all the pictures are posted as comments to the available “7 cells” in the spreadsheet. Is there a loop sequence that would grab each picture from the picture folder in turn and place them sequentially as a comment in the appropriate “7cell” of column F on the worksheet? Also can the comment box be resized to 3.55” X 4.83” within the loop? Any help from you gurus is greatly appreciated.

Thanx in advance
Lukas

[Al Chara]

As far as I know, you cannot add a picture to a comment. Please, someone prove me wrong.

[Jack in the UK]

HI guys --

Picture as a comment odd that cant be none???

Of cause you can.. BUT if picture is added you can really add text unless you REALLY REALLY mess about, best then you would need GOOD Picture editor, Paint Shop or Photshop for the rich guys out there.

BUT lets set a case:
I have 15 staff and list them in exce; in say col A.. now each name in Col a i want to add a picture, ok easy to add coment box, but replace yelllow square with person picture YES.. if not exact situation adapt.. but gets the reason over..

No set up a Directory with Staff 1 2 3 4 pictures easy name ie Jacks Picture 5 April 2002 ID or what ever.. now the trick...

select Jacks name and add coment as normal, it will be outlines and ready to input you notes, but right click on the outer highlighted box, you notice the curesr changes to a cross. by now you should see a pop up box - select format comment. again a nother but different box, select colours and lines tab. Select drop down box for colours notice the fill effect down the botton select that - new pop up appears. now choose picture tab - not a great deal happens here but see the button says select picture (do so for owr ner directory we put all the oictures in. Sample will show to check.. click OK and done see its changed..

hide the comment as usual and when you hover on cell name comment picture appears..

Doe that help?

[Al Chara]

Good trick, Jack. I never knew that. I wonder why Excel hides that so well.

[Lukas]

thanks Al and Jack for your response. What i am really looking for is a way to automate this process through a macro as originally outlined. So if there is anybody else out there with please share your knowledge

Lukas

[Ivan F Moala]

On 2002-04-04 19:42, Lukas wrote:
thanks Al and Jack for your response. What i am really looking for is a way to automate this process through a macro as originally outlined. So if there is anybody else out there with please share your knowledge

Lukas

Have a look @ this thread to do what you want
http://www.mrexcel.com/board/viewtop...c=1773&forum=2

If you require help then Post

[Yogi Anand]

Hi Jack:
Thanks for a detailed explanation on how to make use of the Picture aspect set deep into COLOUR>FILL_EFFECTS!

I knew of the COLOUR|FILL_EFFECTS part, but hadn't thought about making use of it as a comment -- I can see a lot of possibilities with this. T H A N K S !

[Lukas]

Thanks for your pointer Ivan. However, i am really a novice at this. Any additional assistance you or anyone else could provide would be most welcomed
Lukas

[Derek]

Hi Lukas

I do not know if you can write code to access pictures from outside Excel, but here
are some ideas that might help you.
Create an excel file called Pictures.xls
In Sheet1 of this workbook:
In A1 insert a comment with picture 1. Format the comment and stretch the picture to the dimensions you require
Copy A1, select A2:A58 and Paste Special / Comment (so all comment formats are the same)
Change the picture in the comments in A2:A58 so all pictures are represented

In the workbook where you want the picture comments to appear (I have used column F
in Sheet1 of a workbook called Mybook.xls) create a macro button with this code:
You will have to change the names of the workbooks and sheets to match the names of yours.


Application.ScreenUpdating = False
Count = 1
For Each cell In Range("F:F")
If cell.Value = 7 Then
Range("A1") = cell.Address
Windows("Pictures.xls").Activate
Sheets("Sheet1").Select
With Sheet1
Range("A" & Count).Select
Selection.Copy
End With
Windows("Mybook.xls").Activate
Sheets("Sheet2").Select
Range(Range("A1").Value).Select
Selection.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Count = Count + 1
End If
Next
Application.CutCopyMode = False
Range("A1").ClearContents
End Sub

This will place each picture comment in turn into each cell containing 7 in column F.

You will need to have both workbooks open. It is probably possible to do this with the Pictures workbook closed but I think you need to include the full path to it in the macro (never done that so don't know)

Note, this macro also uses cell A1 to store the active cell address for pasting. If A1 is not convenient, change it to somewhere else.

Hope this helps you
regards
Derek






[ This Message was edited by: Derek on 2002-04-06 04:40 ]

[Lukas]

Thanks Derek. I appreciate your help. I did some browsing and came across this old post by Ivan. If it could be combined with your code for looping the picture placement process i think it would do the trick. This would eliminate having to run this macro for every picture posted.

Sub Pict_Comments()
Dim HasCom
Dim Pict As String
Dim ImgFileFormat As String
Dim Ans

Set HasCom = ActiveCell.Comment
If Not HasCom Is Nothing Then ActiveCell.Comment.Delete
Set HasCom = Nothing

ImgFileFormat = _
"Image Files (*.bmp;*.gif;*.tif;*.jpg),*bmp;*gif;*.tif;*.jpg"

GetPict:
Pict = Application.GetOpenFilename(ImgFileFormat)
'Note you can load in any nearly file format
If Pict = "False" Then End

Ans = MsgBox("Open : " & Pict, vbYesNo, "Use this Picture")
If Ans = vbNo Then GoTo GetPict

With ActiveCell
.AddComment
.Comment.Visible = False
.Comment.Shape.Fill.Transparency = 0#
.Comment.Shape.Fill.UserPicture Pict
End With

End Sub

Lukas