Highlight on mouse over and display text

[MrPig]

Hi all,

I'm wondering if there's a way with VBA to make the mouse highlight objects, and then produce a hovering box containing results that are drawn from another worksheet?

To help explain, I have a picture of a world map, and a list of contacts, I've altered the cells to fit the countries, but would now like to have it so that the merged cell highlights when I mouse over, and that a box appears when I click - containing the name of the contact from the other worksheet.

Possible?

 

[combo]

The worksheet is not the best tool to produce this result. You could try this with chartsheet. As a starting point try the following:
- select an empty cell,
- click the chart wizard, without any actions finish it. An empty chart should be created,
- right-click the chart, choose the location as separate chartsheet,
- right-click the tab, choose 'view code' and paste the following:

Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
Application.StatusBar = x & " " & y
End Sub

This is a simple trapping of the mouse move event, that is available for chart. You can add graphic objects. Testing mouse position and object coordinates can be the starting point for messaging to the user.
To complete the task you need to lock user interface, you need more events too (MouseDown, Activate/Aeactivate).

combo

 

[SkipVought]

Hi,

Excel does not give you mouse events in a drawing object, shape or in the sheet object.

However, mouse events are available in a Chart Object. However, embedded chart objects do not have the feature readliy available. You must add a Class Module to expose these events.
[help]
Using Events with Embedded Charts
See AlsoSpecificsEvents are enabled for chart sheets by default. Before you can use events with a Chart object that represents an embedded chart, you must create a new class module and declare an object of type Chart with events. For example, assume that a new class module is created and named EventClassModule. The new class module contains the following code.

Public WithEvents myChartClass As Chart

After the new object has been declared with events, it appears in the Object drop-down list box in the class module, and you can write event procedures for this object. (When you select the new object in the Object box, the valid events for that object are listed in the Procedure drop-down list box.)

Before your procedures will run, however, you must connect the declared object in the class module with the embedded chart. You can do this by using the following code from any module.

Dim myClassModule As New EventClassModule

Sub InitializeChart()
Set myClassModule.myChartClass = _
Worksheets(1).ChartObjects(1).Chart
End Sub

After you run the InitializeChart procedure, the myChartClass object in the class module points to embedded chart one on worksheet one, and the event procedures in the class module will run when the events occur.

[/help]

Bottom line: put your map pix in a ChartObject Object and enable these events.

Skip,
[sub]
[red][/red]
[/sub]

 

[SkipVought]

You might also check out the GetChartElement Method - xlShape ElementID.

Skip,
[sub]
[red][/red]
[/sub]