Change color of cell when moving mouse over it

Delton · May 28, 2004

Hello,
The Excell Board "Topic" column changes color as you hover over it without selecting it. Is this an Excel/VB code? I have read about similar events here before, but the last one I tried required actually selecting a cell. If this has been previously discussed, does anyone recall the topic name. Thanks, Delton.

Travis · May 28, 2004

I dont believe this is possible without selecting the cell

Tom Urtis · May 28, 2004

Impossible and cannot be done. Excel does not support that functionality over naked cells.

It sounds like you want to just glide or position your mouse over cell(s) and have the interior color of the cell be changed, without selecting (clicking on) any cell(s). That is not possible to accomplish with naked cells. The closest you can come to that is a MouseMove event over a transparent command button, label, or some other ActiveX control set on top of a cell or range, whose properties, unlike spreadsheet cells, respond to MouseMove. That would have it's drawbacks too, among them is the increased difficulty in selecting the cell(s) with a mouse when a transparent control is placed over it like a plexiglass shield.

Jaafar Tribak · May 28, 2004

Hi,

Below are two procedures that shoul be added to 2 Buttons. On e for starting this functionality to cell A1 and the other button for stopping it.

Note that this slows downn the system:

Public Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long

Public Type POINTAPI
x As Long
y As Long
End Type
Dim lngCurPos As POINTAPI
Dim Cancel As Boolean

Public Sub ChangeCellColor()

Canel = False
lngA1Left = ActiveWindow.PointsToScreenPixelsX(Range("a1").Left)
lngA1Right = ActiveWindow.PointsToScreenPixelsX(Range("a1").Offset(0, 1).Left)
lngA1Top = ActiveWindow.PointsToScreenPixelsY(Range("a1").Top)
lngA1Bottom = ActiveWindow.PointsToScreenPixelsY(Range("a1").Offset(1, 0).Top)

 Do
 GetCursorPos lngCurPos
 If lngCurPos.x > lngA1Left And lngCurPos.x < lngA1Right Then
 If lngCurPos.y > lngA1Top And lngCurPos.y < lngA1Bottom Then
 Range("a1").Interior.Color = vbRed
 Else
 Range("a1").Interior.ColorIndex = 0
 End If
 End If
 DoEvents
 Loop Until Cancel = True

End Sub

Public Sub CancelProcedure()
 Cancel = True
End Sub


Hope this helps.

Delton · May 28, 2004

Thank you all for the clarification. It was a question of curiousity more than need, but I was compelled to ask anyway. It always nice to have new toys. I will try the rafaaj2000 post to see what it will offer then.

Travis · May 28, 2004

Delton said:
I will try the rafaaj2000 post to see what it will offer then.

This adds the functionallity to A1 only...but will work as desired for that cell.

Greg Truby · May 28, 2004

Son of a gun, Jaafar, I'd seen this question here before and had seen responses like Tom's (I think verbatim); so I was very surprised to see it done! Kudos!

FWIW - I tried to get this to work with a larger set of cells. While it appears to "sort of work" the results are "spotty" at best - at least with my CPU...

Public Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long

Public Type POINTAPI
 x As Long
 y As Long
End Type

Private Type Coordinates
 lngLeft As Long
 lngRight As Long
 lngTop As Long
 lngBottom As Long
 rngCell As Range
End Type

Dim lngCurPos As POINTAPI
Dim Cancel As Boolean

Public Sub ChangeCellColor()
 Dim r As Range, coorCells() As Coordinates, l As Long, c As Range
 Set r = Selection
 ReDim coorCells(r.Count)

 Cancel = False

 For l = 1 To r.Count
 Set c = r.Cells(l)
 With ActiveWindow
 coorCells(l).lngLeft = .PointsToScreenPixelsX(c.Left)
 coorCells(l).lngRight = .PointsToScreenPixelsX(c.Offset(, 1).Left)
 coorCells(l).lngTop = .PointsToScreenPixelsY(c.Top)
 coorCells(l).lngBottom = .PointsToScreenPixelsY(c.Offset(1).Top)
 End With
 Set coorCells(l).rngCell = c
 Next l
 r.Interior.ColorIndex = 24

 Do
 For l = 1 To r.Count
 GetCursorPos lngCurPos
 If lngCurPos.x > coorCells(l).lngLeft And lngCurPos.x < coorCells(l).lngRight Then
 If lngCurPos.y > coorCells(l).lngTop And lngCurPos.y < coorCells(l).lngBottom Then
 coorCells(l).rngCell.Interior.Color = vbYellow
 Else
 coorCells(l).rngCell.Interior.ColorIndex = 0
 End If
 End If
 DoEvents
 Next l
 Loop Until Cancel = True

End Sub

Public Sub CancelProcedure()
 Cancel = True
End Sub

Tom Urtis · May 28, 2004

Jaafar - -

I admire your persistence with this topic, on this and other threads. I am not trying to deflate yours or anyone's enthusiasm in finding a solution, but I still say what you want to do cannot be done. I hope I am wrong, and if anyone, including me, comes up with code to do this, I'll send you a 12-pack of locally brewed Anchor Steam Beer and a couple loaves of genuine sourdough bread, fresh from the markets at Fisherman's Wharf here in San Francisco where I live.

Even the code you posted does not really work for cell A1, nor does it get triggered by cell A1 being the cell under which the mouse is hovering. I know cell A1 gets shaded to give the illusion that the code acts on cell A1, but really it does not. Consider two items:

(1)
What your code does is play off cell A1 because that cell happens to be in the upper left corner of the worksheet on most systems. The POINTAPI structure describes a location with X and Y coordinates as you have used. Left and Top fields describe the corner of the cell (a rectangular object), and because cell A1 is usually in the upper left corner of a worksheet, it is uniquely positioned to have its Left and Top POINTAPI fields reliably identified on most systems.

However, you can test for yourself why cell A1 really is not the trigger itself. Go to Tools > Options > International tab, and select "View current sheet right-to-left". That will place A1 where usually cell IV1 is, and reverse the order of the columns. Now try your code by hovering your mouse over cell A1. Unfortunately your code will fail because now the top and left fields mean nothing for cell A1, as its top left corner now abuts the top right corner and edge of a cell (B1), not the edge of the active window.

This brings us to my second point:

(2)
With the international right-to-left option still in place, try the code again by revising it for cell IV1 instead of A1. Unfortunately, it will fail again, this time because even though cell IV1 is in the upper left corner or the screen, its upper left corner (and left edge) is still manipulate-able...it is not rigid like cell A1's left edge is. Test this on your current standard worksheet by placing your mouse on the left edge of the column A header. You do not get the double-ended arrow to allow for expansion of column A left-ward, as you do to expand any column (*including IV*) left-ward and right-ward.

The crux of this second point is, the POINTAPI structure does not have a Right or Bottom field to accompany its Top and Left fields. Its counterpart RECT function does have those two additional fields, but they speak to the object's size, not its location. Unless you know where one cell ends (location), not just how big it is (size), the act of a mouse hovering over a cell does not allow for determining either field when all other borders (other cells) are unknown.

That means, unfortunately, your code does not treat A1 because it is A1, but because it is in the top left corner in a standard left-to-right environment. That further means your code is not triggered by your mouse hovering over any cell, which brings us back to my belief that naked cells do not and cannot trigger a mouse move event, upon which any formatting or manipulation of such cells can take place.

Now, maybe some applicability can be had by a user who coincidentally, but not likely, has cell A1 and only cell A1 in mind for any such format change, but outside of that, I think the other 16,777,215 worksheet cells are out of luck.

My offer still stands for SF beer and sourdough if we can find a solution, and I'll poke around, but it seems like a bleak prospect. Let me know if you find anything that does work, and certainly I will do the same. Good luck to us all with this one !!

Jaafar Tribak · May 29, 2004

Tom ,

Thanks for the detailed and enlightning explanation.I agree with you in that this is by no means an accurate solution and as I mentioned in a previous post on a similar topic a couple of days ago, I am aware the code doesn't work accuratly for other cells.However I find subjects like these so exciting and educational that I am constantly trying new methods.

Looking forward to sampling the Brew

Regards.

Delton · May 29, 2004

Ahh....Tom.... since it was my question this time around.... am I elligible for one of those beers too? Kind of like a "class action" thing for being caught up in the collateral frenzy?

Change color of cell when moving mouse over it

Delton

Board Regular

Excel Facts

Travis

Well-known Member

Tom Urtis

MrExcel MVP

Jaafar Tribak

Well-known Member

Delton

Board Regular

Travis

Well-known Member

Greg Truby

MrExcel MVP

Tom Urtis

MrExcel MVP

Jaafar Tribak

Well-known Member

Delton

Board Regular

Similar threads

Forum statistics

Share this page

Change color of cell when moving mouse over it

Board Regular

Excel Facts

Well-known Member

MrExcel MVP

Well-known Member

Board Regular

Well-known Member

MrExcel MVP

MrExcel MVP

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock