Not so picture perfect
Not so picture perfect
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Not so picture perfect

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?



    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    Kind Regards,
    Ivan F Moala From the City of Sails

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 !
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  10. #10
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com