Excel question - Hover over image event VBA

[chirayu]

Hi All,

I just wanted to know if its possible to do a hover over menu in Excel like there are on websites.

I know its possible with regards to cell hover but I wanted to know if there is a way to hover on an image and have a menu bar pop up and disappear once you hover off that image.

From the various articles I have read online - they all say you need an image click event and there is no hover over option for images.

I used this to be able to show a menu once the relevant tab on the main title bar is clicked in my dashboard and then used worksheet change as a trigger to hide the menu (my menu is a group of hyperlinked image buttons grouped together)

But I thought I might check in with the Excel Ninjas and Chandoo & veritable forum users to see if its possible to code a hover over event for images.

Thanks & Regards
Chirayu

 

[lohithsriram]

Hi Chirayu,

Make me understand better!!!

Is this image you are placing on the Worksheet and mouse over it should show/hide the menu? Image Activex Control does have the mouse move event to make it work as hover. You can track the event arguments (X & Y) to determine the cursor position and have your code to show/hide menu.

Are you referring to the shapes in Excel which does not hold any events? Even then, we can place this shape on the image Activexcontrol and make the shape behave like the Image control.

Sorry, if I am talking different here.

 

[chirayu]

Hi Lohitsriram,

Yes I am talking about the Excel shapes - I have made separate buttons using the shape and grouped them in a banner (pic attached)

basically what I want is to be able to hover over each button and bring up a menu, then when I hover off - it should disappear. Like a website menu where the hover bring up a dropdown menu which u can click to navigate elsewhere

 

[SirJB7]

Hi, chirayu!

Give a look at this:
http://chandoo.org/forum/threads/zoom-in-on-micro-pivot-charts.5896/

Regards!

 

[lohithsriram]

Hi Chirayu,

I have a restriction to upload the file and hence cannot do that. I will try my best to explain what i thought.

Shapes for sure will not hold any events. We need Activex controls (button or an image) to capture the mouse move (hover) events. So, for your need we require 2 activex images and your shape.

Say Image1, Image2 and the shape (eg., Monthly snapshot)

Image2 has the same dimension (width and height ) as your shape.
Image1 is slightly bigger than Image2 in size. We will fit the Image2 into Image1(like a rectangle inside a rectangle - appears to be one rectangle with thick border).

Place the Image1, set the border and the color to None(Visible property should be true otherwise event will not fire). Set the order to "Send to Back".
Place the Image2, inside Image1 and set the border and color same as Image1 to hide its presence. place your shape above Image2. So now, mouseover your shape is actually Mouseover the Image2, code in Image2 Mouseover to show the menu.

On Image1 mousemove code to hide the menu. So the mousemove event always trigger the Image1mousemove before the Image2mousemove as the Image2 is covered in all sides by Image1. Mouseover the shape to show the menu. Move the mouse focus out of the shape and the menu gets hidden, because the Image1 has an event to hide the menu and the mouse should move over Image1 before it gets focused on anywhere else.

Hope my description is making sense!!!

 

[chirayu]

Hi Lohitsriram,

I followed your instructions - works like a charm. Now to apply it on my dashboard. Thanks

 

[lohithsriram]

Brilliant!!! Glad to hear that the Idea worked.

Also remember to have the menu visible=False on the Worksheet_SelectionChange event, just incase if the mouse move is too fast and the Image1_MouseMove doesn't trigger. This ensures the menu go off when you are out of the shape and before doing any other activity on sheet/other sheets.

 

[chirayu]

Hi Lohit,

I added this nugget into ThisWorkbook Thanks for the advice

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
ActiveSheet.Shapes("Shape1").Visible = False
End Sub

 

[cbone1234]

Hi Lohitsriram, your example is great!