Display and hide image on sheet on mouse hover on a cell

[neeraj_chow]

In excel 2007, I have an embedded image on a sheet which I want to make it hidden by default. When someone hovers over a particular cell say cell B3, then image should be displayed below this cell (sort of hyperlinking) and when the cursor or activell cell is not in B3 it should be hidden again.
What steps/technique/VBA code will make it happen ?

 

[Andrew Poulsom]

You can us a Comment with a picture added:

http://www.contextures.com/xlcomments02.html#Picture

 

[neeraj_chow]

Thanks for the great tip. I had read about it few years back and the concept had somewat slipped from my mind. Now that this has been brought back in focus, I can now create lots of things which I had in mind.

 

[Rick Rothstein]

You can us a Comment with a picture added:

http://www.contextures.com/xlcomments02.html#Picture

Thanks for the great tip. I had read about it few years back and the concept had somewat slipped from my mind. Now that this has been brought back in focus, I can now create lots of things which I had in mind.

Another way to do what you want is with a transparent ActiveX Label control. I will assume you want this functionality on Sheet2 and that your Label is named Label1 and that the picture you want to display is named "Picture 2" (change these to match your actual setup, in the code below, as needed). Put the following code in the ThisWorkbook code module (double click ThisWorkbook in Project Window inside the VB editor) as it places and sets the necessary properties for the Label Control and the Shape containing your picture...

Private Sub Workbook_Open()
  Const LabelCoverCell As String = "B3"
  With Sheets("Sheet2")
    With .Label1
      .Left = .Parent.Range(LabelCoverCell).Left
      .Top = .Parent.Range(LabelCoverCell).Top
      .Caption = ""
      .BackStyle = fmBackStyleTransparent
    End With
    With .Shapes("Picture 2")
      .Left = .Parent.Range(LabelCoverCell).Left
      .Top = .Parent.Range(LabelCoverCell).Offset(2).Top
      .Visible = False
    End With
  End With

I used cell B3 as the "trigger" cell for showing the picture... you can change that to any cell you want and the code will place the label and picture accordingly. Note that I placed the top-left corner of the picture two cells down from B3... it is important to leave a noticeable space between the picture and the "trigger" cell, otherwise the picture might not get hidden when the mouse leave the "trigger" cell area.

Okay, now next, place the following code in the sheet module for the worksheet you put the label and picture on (right-click tab name for the sheet and select View Code from popup menu)...

Private Sub Label1_Click()
  ActiveSheet.Label1.TopLeftCell.Select
End Sub

Private Sub Label1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, _
                             ByVal X As Single, ByVal Y As Single)
  With ActiveSheet
    .Shapes("Picture 2").Visible = X >= 0 And X <= .Label1.Width And Y >= 0 And Y <= .Label1.Height
  End With
End Sub

That is it... save the workbook as a macro enabled workbook, close the workbook and then reopen it (to force the Workbook Open event to fire). If you changed all the necessary names (I made them all red for you to more easily see), your picture will appear when you place the mouse cursor over the "triggger" cell and disappear when the mouse cursor leave that cell.

 

[RayFrye]

Hi, I know I am a few years late to this discussion. I get the Workbook_Open, picture offset etc. The code to show the picture based on mouseover works as expected. But I do not see what triggers the picture to not be shown. But I see no automated trigger to hide the picture once it is turn visible. I added WorkSheet_SelectionChange to test the cell location and hie the picture. An auto hide when the mouse leaves the Label would be nice.