Thursday, February 07, 2008 5:27 PM
I"ve got a VBA-driven Excel 2003 sheet that contains a control button. The spreadsheet updates every second or so (independent of the control button) which causes the hourglass to display very briefly and then change back to the default pointer.
When I hover over a control button, the pointer turns to an hourglass on the next update cycle (as usual). However, the pointer then change back to the default pointer until I move the pointer off of the control button. As long as the pointer stays over the control button, it is locked to an hourglass - independent of the update state of the worksheet.
Any ideas on how I can allow normal pointer operation even when hovering over a control button (or simply disable the hourglass change altogether!).
Thanks in advance.
Thursday, February 07, 2008 7:48 PM
If I have understood your question right…
You have to specify the type of pointer displayed when you position the mouse over the ActiveX controls on the spreadsheet.
Select Design Mode on the Control Toolbox Toolbar and click the control button to work with. Select the Properties in the Control Toolbox. This takes you straight to the properties window for the control button. Here you can set the MousePointer property when you want to indicate changes in functionality as the mouse pointer passes over the control button. Change the MousePointer property to fmMousePointerDefault.
Thursday, February 07, 2008 7:58 PM
Thank you kindly for your reply.
The property on this control is set as you have described.
Perhaps I can try and re-describe what's going on in different words:
When the VBA code is not firing off recalculations (and the brief display of an hourglass), the mouse pointer behaves properly. Even when the VBA code is actively running, the pointer over the object (the control button) is correct UNTIL the hourglass displays. Then the mouse pointer simply stays an hourglass, but only if the pointer remains over the control button.
As soon as I move the mouse pointer off of the control button, the hourglass disappears and all is normal again...even if I re-hover over the button. Then, the next time an hourglass is generated, it stays an hourglass again until the pointer is moved off of the button.
Friday, February 08, 2008 8:45 AMHi,
When the workbook updates is it running one of your codes? If so you could try adding code to reset the mouse pointer at the end of the routine.
ActiveX controls can be a pain sometimes. Does the same thing happen for buttons off of the Forms toolbar?
Friday, February 08, 2008 2:51 PM
Thanks for the reply, Andy.
I added the cursor reset everwhere in the code following where a calculation is initiated (the sheet is set for manual re-calc). No joy.
The cursor behaves properly over all other (non-control button) areas of the worksheet, and when hovering over any toolbar (that is, the cursor just blinks and hourglass and then returns to normal).
I tried changing the properties of the control button to hardwire an Arrow cursor, but the behavior is unchanged (application.Cursor = xlDefault lines commented out).
I might just have to chock this one up to ActiveX funniness.
Sunday, March 30, 2008 12:16 PM
I ran into the exact same problem, and found your note.
The real problem is when the SIZE OF THE BUTTON is big enough
so that the entire mouse pointer is over top of the button,
without any part hanging over the outside boundaries.
Then I came up with this fix, this seems to work ok in my case, maybe yours too.
Application.ScreenUpdating = True
I am not sure WHEN this should be done, upfront or at then end.
I did it at the the end, and the Cursor pointer changed back
(while entirely sitting on the button).
Private Sub btnUpdate_Click()
Application.Cursor = xlWait
Application.Cursor = xlDefault
Application.ScreenUpdating = True
I hope it helps,