# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  Custom message with mouseover

## zasskar

Hello,

I was wondering if anyone has information about a VB script that could display custom information when the mouse is hovered over a bar/column within a pareto chart?

For example, in my example, I show that there are 5 BMWs, 3 Ferraris, and 3 Yugos.  If the user put their mouse over one of the bar/columns for BMW, Id want to display (in a box, as a cloud, etc) the contents of the Root Data tab for BMW, which happens to be BMW-Nice.  Is this possible?

Currently, if the mouse is hovered over the white BMW bar columns, I get the Microsoft output:
Series White point BMW Value: 3

Thank you!
michael

----------


## MSP77079

Yes, this is possible.

If the chart is a separate sheet in the workbook, it is fairly simple.  You can use any of the Events (one of which is Mouse Move, so your routine would run every time the mouse is moved).

The Event determines when your routine will run.  The next part is knowing where the mouse is pointing.  To determine this, use the GetChartElement Method of the Chart object.  This Method returns information telling you what part of the chart is being pointed at, and you can decide to ignore move and only respond to some.

I use this quite a bit to allow the user to (for example) click on a point in a scatter chart, then tell them a bunch of stuff I think they might want to know about that point.

If the chart is NOT a separate sheet in the workbook, all of these same principles apply; but, you have to do more work to make it happen.  My solution for this is to create a Class Module.  Once you get to the Chart (through the ChartObject, through the Worksheet), it is exactly like the Chart was a separate sheet.

If you haven't done anything like this before, I would suggest starting with the separate Chart sheet and being sure you have what you want before attempting the Class Module.

----------


## MSP77079

Here is the code to insert on the Chart1 object code window.




```
Please Login or Register  to view this content.
```


keepA and keepB are declared outside of the Event procedure so that you do not get the message box again until you mouse over a different model or different color.

You would want to replace the MsgBox with something more elaborate, I assume.

----------


## zasskar

First off, thank you, thank you, thank you!!!  Thank you for the explanation, and thank you for the code.  Im new to VBA, so this is more advanced for me but I have a programming background which will allow me to break this down and understand what you did.

Just out of curiousity, is there a way to display a message that does not use the message box?  The reason is that with the message box the user always has to press enter to acknowledge the message.  Would it be possible to display a message when the mouse is over a bar/column and then this message disapears when the mouse goes away?

I thought that the status bar might work for this, but after experimenting I think another solution would be better.  The examples I found for a message box always seem to require user interaction to close/acknowledge the box.

Thank you again so much!

----------


## MSP77079

Yes, definitely.  I knew you would not want a message box.  You want a floating message that appears and disappears.  I'll work on that and post it here.

----------


## Bryan Hessey

> Yes, definitely.  I knew you would not want a message box.  You want a floating message that appears and disappears.  I'll work on that and post it here.



Hi MSP77079,


the attached, from http://www.bmsltd.co.uk/Excel/SBXLPage.asp might help save you some time in your message building.

hth
---

----------


## MSP77079

Hi Bryan, 

Haven't looked at the attachment yet.  I am sure it is better than what I came up with.

Meanwhile, Michael, here is what I came up with ... replace the code above with the code below (on the Pareto chart's code window).  To use this, you need to have first created a shape (I chose a rectangle, but you can make it any auto shape you like) and Name it "CommentBox" (easiest way to do this is to use the Name box that is to the left of the Excel formula bar ... always remember to press Enter after typing in a name or the name will not "stick").




```
Please Login or Register  to view this content.
```

----------


## MSP77079

Bryan, that is a great form you created!  I am sure I will find it useful.  I will be sure to credit you in any code I use it with.

Michael, take at look at Bryan's workbook.  It might appear too "formal" for the impact you are trying to achieve.  (Although, for MY purposes, it looks much more professional than an autoshape!)

If you prefer the professional look and want me to put it into your workbook, let me know.  Shouldn't be a problem.

----------


## Bryan Hessey

> Bryan, that is a great form you created!  I am sure I will find it useful.  I will be sure to credit you in any code I use it with.
> 
> Michael, take at look at Bryan's workbook.  It might appear too "formal" for the impact you are trying to achieve.  (Although, for MY purposes, it looks much more professional than an autoshape!)
> 
> If you prefer the professional look and want me to put it into your workbook, let me know.  Shouldn't be a problem.



Would that I could claim such code, it was found at Stephen's site as mentioned, I simply thought it might help as 'floating message boxes' are not (to my knowledge) commonplace items.

---

----------


## zasskar

Thank you both MSP and Bryan!

I'll try to make Bryan's solution work if I have time to make my material look more professional but right now the MSP solution is exactly what I originally had in mind.

Thank you again very much!
michael

----------


## zasskar

Hello,

In Post #7, MSP gave me some great code which was quite advanced for me (I'm fairly new to VBA).  I've since played/experimented/researched and now all of it makes sense.   The only thing I don't understand is why these variables don't have to be declared:

myColor = ws1.Cells(4, a + 1)
myModel = ws1.Cells(b + 4, 1)

Is this because myColor/myModel become strings?  Or is this because we are using ws1?  

I would have thought I'd need to do something like:
Dim myColor As String

but my code works great w/o having to do that.  Why?


Thanks for your answer!
michael

----------


## Bryan Hessey

> Hello,
> 
> In Post #7, MSP gave me some great code which was quite advanced for me (I'm fairly new to VBA).  I've since played/experimented/researched and now all of it makes sense.   The only thing I don't understand is why these variables don't have to be declared:
> 
> myColor = ws1.Cells(4, a + 1)
> myModel = ws1.Cells(b + 4, 1)
> 
> Is this because myColor/myModel become strings?  Or is this because we are using ws1?  
> 
> ...



Hi,

as you do not have 'Option Explicit' at the start of your code page the code will define variables to suit.

If you run with 'explicit' then you will need to define the variables x, myColor etc

hth
---

----------


## MSP77079

Good answer by Bryan already.  Let me add 2 cents.  

I will almost always declare (dimension) objects because then the Intelli-sense kicks in ... so, as soon as I type a "." after the variable, a drop down box appears with all of the properties and methods for that object.  It makes it a lot easier to code correctly.

It is considered good form to always use Option Explicit.  But, I guess I do not have good form.

----------


## zasskar

Thank you both very much!  I just couldn't figure it out!   :Smilie:

----------


## Bryan Hessey

> Good answer by Bryan already.  Let me add 2 cents.  
> 
> I will almost always declare (dimension) objects because then the Intelli-sense kicks in ... so, as soon as I type a "." after the variable, a drop down box appears with all of the properties and methods for that object.  It makes it a lot easier to code correctly.
> 
> It is considered good form to always use Option Explicit.  But, I guess I do not have good form.



Thanks, I always wondered what triggered that.  I find Option Explicit (when it wasn't used) to be a last-resort check for a sub that just doesn't seem to do the right thing (usually a mis-spelling etc)

Good Form would be nice - perhaps every new module should add that as a default, and require it's removal if not required, this would avoid the forgottten entry.

Cheers
---

----------


## MSP77079

Bryan, 

You _can_ turn the option on or off to automatically add Option Explicit to every new Module.

In the VB Editor, use Tools >> Option; see the Editor tab.  Click "Require Variable Declaration".  Then you are stuck with it until you remember to turn the darn thing off again.

Access ships with this already set on.  The other Office products ship with this option off.

----------


## Bryan Hessey

> Bryan, 
> 
> You _can_ turn the option on or off to automatically add Option Explicit to every new Module.
> 
> In the VB Editor, use Tools >> Option; see the Editor tab.  Click "Require Variable Declaration".  Then you are stuck with it until you remember to turn the darn thing off again.
> 
> Access ships with this already set on.  The other Office products ship with this option off.



Good to see MS keeping up to the standard expected.

Thanks for that, the only option that wasn't selected, I'll try that for a while.
---

----------


## sam314159

> Hi Bryan, 
> 
> Haven't looked at the attachment yet.  I am sure it is better than what I came up with.
> 
> Meanwhile, Michael, here is what I came up with ... replace the code above with the code below (on the Pareto chart's code window).  To use this, you need to have first created a shape (I chose a rectangle, but you can make it any auto shape you like) and Name it "CommentBox" (easiest way to do this is to use the Name box that is to the left of the Excel formula bar ... always remember to press Enter after typing in a name or the name will not "stick").
> 
> 
> 
> 
> ...




I know I am reviving a pretty old thread here, but this is exactly what I am trying to do except that I have my charts in a worksheet not in an individual "Chart" tab. 

The above code works perfectly for me when the chart is in its individual tab, but I have my charts in a worksheet and I am not sure how to get started with this? Any help would be greatly appreciated guys.

(If it would be better for me to start a new thread instead of reviving an old identical one, let me know please and I can start a new thread)

----------


## shg

Welcome to the forum, Sam.

Please take a few minutes to read the forum rules, and then start your own thread. Also, please don't quote whole posts.

Thanks.

----------

