Thread: MouseOver cell that makes apear and disappear an image

Hello,
Please Help

I would like to do a routine in VBA on excel 2007 that makes the following:
1- When the mouse is over a cell it pops-up a window that shows a picture;
2- When the pointer is removed from the cell the window that contains the picture closes;
It is very important that the picture that is shown be adressed by a relative path from the folder in which is the excel file.
The pictures can change; this is the reason that the picture should be addressed by a path.

Thanks a lot

Is the path address in the cell?

This code will find the .jpg in the active workbook folder and place the address into cell G1.
It will then add a comment into A1 and use the address in G1 as the source.

Sub AddPicToCellComment()
    Dim r As Range, i As Range

    Set r = Range("G1")
    Set i = Range("A1")


    With Application.FileSearch
        .LookIn = ThisWorkbook.Path
        .Filename = "*.jpg"
        If .Execute > 0 Then    'there is a .jpg
            r = .FoundFiles(1)
        End If
    End With

    With i
        .ClearComments
        .addComment
    End With

    With i.Comment.Shape
        .Fill.UserPicture r
        .Width = 600
        .Height = 400

    End With


End Sub

Just quoting here, but a similar topic came up yesterday in another thread

Originally Posted by Andy Pope

There are no mouse movement events for a workbook.

Activex controls on the worksheet have mouse events as do controls on a userform.

Edit: Scratch that then, comments are obviously the exeption. Nice!

Dave,

Sorry. It gives me an error "run-time error 445" at the linecode "With Application.FileSearch"

Thkx

EDIT: Thkx Dave. I've solved the issue. I realized that Application.Filesearch is no longer available in excel 2007. I got it to work with this from Roy's blog http://excel2007tips.blogspot.com/20...h-in-2007.html

Thank you

I did not know that, I had just assumed that XL'07 could run XL'03 VBA, thanks for the link.

Could you supply the code so people with XL'07 can use it.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save