Excel 2007 VBA Mouse Move Event For Shading

Escanaba
Escanaba used Ask the Experts™
on
Hello,

Is it possible to construct a VBA code using the mouse move event so that when the end-user moves the mouse over a section of cells the background iswhite while all other cells on the worksheet are shaded?
On my report I have 4 sections:
Section 1: A2:H27
Section 2: I2:AD14
Section 3: I15:P27
Section 4: Q15:AD27

For an example, if the mouse is resting on any cell in section 1, all other sections are shaded.

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008

Commented:
It is possible but requires VBA code and will impact the performance of the workbook. There is no built-in event handler to do what you want.

Kevin
EscanabaManager - HR Analytics

Author

Commented:
The file is not very large so Im not very concerned about slowing down the performance.  Is this something you can assist in putting together?
Top Expert 2008

Commented:
Let me rephrase. This can be done but it's going to involve VBA code looping in the background - constantly running and slowing down the performance of the workbook. It's also very complex code. Are you sure you want to try this? I do not recommend doing it. I have created some demos illustrating this technique in the past but I have never used them because they are so invasive. It's more of a theoretical exorcise than anything.

Kevin
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

EscanabaManager - HR Analytics

Author

Commented:
If it's that complicated then perhaps not.  I appreciate the advice and will explore some other options.  Thanks.
Top Expert 2008

Commented:
I've got another idea. Do you mind of the users can't actually select the cells? If so, you can place a transparent picture over the sections you want to monitor and capture the mouse move event from the ActiveX picture object. One caveat is that you can't detect when the mouse leaves the picture.

Kevin
Most Valuable Expert 2011
Top Expert 2011

Commented:
It can also be done by subclassing the workbook window and intercepting the mousemove messages. Again, it's really not easy though!!
Top Expert 2008

Commented:
>It can also be done by subclassing the workbook window and intercepting the mousemove messages.

Wouldn't that run the risk of executing code while a cell was open, resulting in an instantaneous termination of the application?
Most Valuable Expert 2011
Top Expert 2011

Commented:
You can run code while a cell is in edit mode.
Top Expert 2008

Commented:
Are you telling me that Excel can determine whether or not managed code is being executed from a Windows timer versus other means? And that it will terminate instantly if the former but not that latter? That seems most odd to me at the moment.
Most Valuable Expert 2011
Top Expert 2011

Commented:
I'm pretty sure I didn't say anything like that! :)
You can run timer code in edit mode too.
Most Valuable Expert 2011
Top Expert 2011
Commented:
Demo file (tested in 2003 and 2007). Don't forget to press Destroy Clock before closing the workbook!


ClockTest.xls
Most Valuable Expert 2011
Top Expert 2011

Commented:
PS Kudos go to Jaafar Tribak for this one!
Top Expert 2008

Commented:
I've heard of alternate realities but I have never actually shifted from one to another. Me thinks that has happened.

There must be some explanation for the wealth of documentation out there that states that you can't do that:

   http://www.cpearson.com/Excel/OnTime.aspx
   http://www.pcreview.co.uk/forums/thread-988072.php
   http://www.justanswer.com/questions/1x1fi-i-have-written-a-macro-in-excel-that-uses-poke-command-to-exchange

While trying different scenarios out with the sample workbook you provided I did get Excel to quit once but could not reproduce.
Most Valuable Expert 2011
Top Expert 2011

Commented:
Twisting reality is all in a day's work for your typical Excel user... ;)
Top Expert 2008

Commented:
Given that Excel does have a propensity to quit immediately when playing such games, although it is obviously not a sure thing, I would never recommend doing so in any kind of production tool. So, while my reality has been shaken a bit, I have to remain of the opinion that this really isn't a solution to any problem.

What I have been able to create without experiencing a crash is a dual timer where the Windows timer calls a managed code routine that instantiates an Application.OnTime timer and then exits. If no workbook/worksheet objects are changed, the risk of a crash is greatly diminished if not eliminated. The Application.OnTime timer is held until any open cell or modal dialog is closed. However, this was an exercise only to see if I could do stupid things from a UDF and I would never consider using such a technique in a production situation.
Most Valuable Expert 2011
Top Expert 2011

Commented:
I remain of the opinion that you need to get out more! 8^P
Top Expert 2008

Commented:
OK
What about using a transparent userform over the range?  You could then create Mouse_Enter and Mouse_Exit events...
EscanabaManager - HR Analytics

Author

Commented:
"I've got another idea. Do you mind of the users can't actually select the cells? If so, you can place a transparent picture over the sections you want to monitor and capture the mouse move event from the ActiveX picture object. One caveat is that you can't detect when the mouse leaves the picture."
Kevin - Sorry for the delay.  I've been away from the office.  Having the users not be able to select the cells will not be an issue.  If you can provide a sample so that I can see how to set it up I can apply it to my worksheet.
Thanks!
Escanaba. If there will not be any scrolling on your worksheet, a transparent userform will allow for mouse exit. If there is scrolling involved then the userform is out of the question unless someone here knows how to make a userform scroll with the worksheet without setting an embedded control as its parent. Another option, if you don't mind using a dll can be found at the following link.  http://www.mrexcel.com/forum/showthread.php?p=2162850#post2162850

These events are exposed.
Private Sub MEvents_RangeMouseEnter(Target As Range)
Private Sub MEvents_RangeMouseExit(Target As Range)
Private Sub MEvents_RangeMouseMove(Target As Range)
Private Sub MEvents_ShapeMouseEnter(Target As Shape)
Private Sub MEvents_ShapeMouseExit(Target As Shape)
Private Sub MEvents_ShapeMouseMove(Target As Range)
Private Sub MEvents_SheetMouseEnter()
Private Sub MEvents_SheetMouseExit()

WS-Mouse-Events.zip
EscanabaManager - HR Analytics

Author

Commented:
Tom - I appreciate the post and still working and putting this together.  Just wanted to give a status update and indicate this has not been forgotten.  I'll touch base soon letting know if this works.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial