Maintain Mouse Position on Button Click

[cmore]

Hi,

I'm working on this spreadsheet and data is entered on the click of a command button. I'm trying to figure out how to make sure that on the click (and mouse over) the cursor will stay in the middle of the button.

All thoughts appreciated?

[Kaper]

Have you seen such code: http://www.ozgrid.com/forum/showthread.php?t=196895

[cmore]

link no open?

[Kaper]

Have you tried other browser or another time? May be there was some short term blackout? Does ozgrid.com open?

[cmore]

Got it. Works now, was weird, I tried 3 diff. browsers before. Thanks. I saw this, but didn't try it's kinda confusing, was praying for something more simple. but I'm gonna go for it. I have to make a few other edits also. So I'll post back to this topic in while when done.

[cytop]

was praying for something more simple

(Comment no longer applies - this is simple )
_____________________________________________________________

After a little contemplation and navel gazing...

What you're asking is not exactly what that code does. You want to click a button, do something then put the mouse back to where it was when the button was clicked? If so, prayers may be answered as you know where the mouse was, and where it has to go.

[cytop]

Even I can understand this:

Add a module, and paste

Please Login or Register  to view this content.

Add a button to a worksheet, doesn't matter if ActiveX or forms buttons, and call the ButtonClick procedure in the click event. This will work for any control you can trap an event for, regardless if on a userform or worksheet

For simplicity this will return the mouse to where it was when first clicked - not centered over the control and is written for a 32 bit version of Office and will need modifying for a 64 bit version - plenty of information about that around the place.

[cmore]

Thanks cytop, we've confirmed that you're smarter than me . To be 100% honest, I just try to usually avoid digging into libraries, and code that would depend on the O.S. running etc. (I'm a conservative, Trump voter from TX) and was wondering if it was avoidable. I see the answer is no. Checking out if I can wrap my mind around it shortly.

[cytop]

try to usually avoid digging into libraries, and code that would depend on the O.S.

Excel does not include any native functionality to work with the mouse pointer (other than changing the cursor to one of a few options) - so there is no alternative. You either have to live with it or not include the feature.

[Kaper]

:-)
I'm pretty conservative too, and try to achieve a lot with formulas, if not, then with built-in functionality, then macros using standard VBA, but unfortunately to achieve non-standard behaviour:
"excel controls mouse, instead of mouse controlling excel"
we have to reach for something from "less standard" arsenal
:-)

[cmore]

Taken. I get what you've done. and agree to your point. The thing/question comes when using methods/properties I'm very unfamiliar with and that's hard for me to research independently. I like to know stuff.

I'm having trouble with this right now on 2 levels

1. I don't understand what's occurring with a few of these

Please Login or Register  to view this content.

I'm also unsure of what's occuring with

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

[cmore]

Also, any thoughts on .PointsToPixel?

[cytop]

Can understand you're nervous about 'non-standard' functions.

However, these are only non-standard for the VBA language. They are actually Windows API (Application Programming Interface) functions. These are what Windows uses to run itself.

Every program you write whether VBA or another language, at a lower level after compiling, uses these - so they are very standard functions. I did mention that the code needs modification for 64 bit but this is only because right now there's a transition from 32 to 64 bit. The last time this happened was back in 1995(ish) when system architecture moved from 16 bit to 32 bit - so that's not really an issue in my opinion.

If you go and record a macro, you should be able to run that code on either a 32 bit or 64 bit Office installation no problem as anything recorded can only be whatever you can do in Excel - in your case you were asking about functionality not natively available to VBA so the only solution is to drop back to API calls, and that's where the re-writes may be needed.

The anatomy of an API call is simple.

First off, you need to declare you're using an API function. This is done in the General Declarations section at the top of the module

Please Login or Register  to view this content.

The Private statement is exactly the same as Public/Private variables. This declaration is only valid for the module it is declared in.
Declare Function/Declare Sub are fairly obvious
FindWind is the Function you are declaring
It is contained in the library USER32(.DLL)
Ignore Alias - it's a little involved and to do with passing different types of parameters.
The remainder is the list of parameters used by the function, exactly the same as parameter in VBA

This is a very simple function.

Please Login or Register  to view this content.

The API works with 'handles', this is simply a number (Type is Long in 32 bits, LongPtr in 64 bits - beginning to see the changes needed now. A 64 bit system can overflow the bounds of a Long) which represents the memory location of the Window definition. This definition includes all the attributes for the Window such as Top, Left, Width, Caption Bar, Back color, if the borders are sizable and so on ad infinitum.

Much like the declaration says, that function Finds a Window handle. It can use either a 'Class name' (irrelevant here) or a Window Caption (It can also use both, but you'll understand that if you start to use APIs)

The userform to find is represented by 'f' and the call to the API uses the caption.

If found, the variable lngHwnd will contain the handle. This is then used for further processing.

Unfortunately, VBA and Windows uses different ways to reference points on the screen. The other 2 functions (PointsPerPixel...) are used to convert from one to the other. You can forget about them for your question as you don't need to convert. You click a button and the current mouse positon is stored in the variable 'p'. This is a standard VBA Type with 2 elements representing the x & y coordinates of the mouse when the button was clicked. After the procedure runs, the mouse is put back to the position stored in p - as the calls to GetCursorPos and SetCursorPos both use the Windows coordinate system there's no need to convert.

As long as you're happy to move the mouse back to where it was when the button was clicked, forgetting about centering, then your question is just about the simplest it can be. You save the Mouse position, you restore the position - 2 very simple API calls as shown in post #7 and you can forget about all the other complicated rubbish.

[cmore]

Please Login or Register  to view this content.

[cmore]

This is what I currently have, but it isn't working correctly, the cursor is landing a bit too high and gives me the wrong results for the x y after running CenterMouseOver

Desired: approx 1894, 508

Getting: 1865, 72

[cytop]

I'd strongly suggest, at this stage, you forget about centering over the control - The differing co-ordinate systems used by Windows and Excel will have you tearing your hair out.

Plus, that code was initially written for controls on a userform - centering the mouse over a control on an Excel sheet is somewhat different but if you want to upload a sample copy of your workbook (Don't need any data or any existing code- just the controls you want to center over included) then I'll take a look when I have the time.

[cmore]

Thanks cytop, verymuch appreciated explanation! I'm biting the bullet on 2 or 3 API functions for the reason you describe. So I think my issue might be that I'm not using a useform and modeling directly in cells?

Finally, yes, agree if I was just taking the mouse click and putting it back where it would be, I'd be done. Unfortunately, I need to do a few things. I have 3 buttons that share relationships. When one is clicked at a certain time I need to move the cursor over to another one respectively. Then on one of the buttons, it's clicked repeatedly, and I need to keep it center on click because the User is needing to limit "misfires" and having to look at the screen while clicking.

So I'm trying to find a way to accurately get the coordinates of the centers of the buttons

One Moment, stripping some data to shrink the workbook

[cmore]

Workbook

Buttons on Testing Sheet

Thoughts very much appreciated.

[cytop]

OK - had a look at that and sorry but I'm just as lost as to how it's used.

It seems you have a Togglebutton which is used as a 'switch' - you do something else repeatedly - including clicking another command button while the Toggle is True.

If so, another mock up for you.

This has a Toggle button and one (but see below) other button. Click the toggle to start then click the other button. You get a message box. Like before, the mouse has to be moved to clear the message, but once it is cleared the mouse moves back to the center(ish) of the command button. This continues until the Toggle is switched off.

Comments in the code - note the comment that controls should be aligned as close as possible to the cell top & left border. It was easier to get the screen position of the cell than the control - and the cell could be determined from the Controls' TopLeftCell property. This is fairly easy to overcome and has been left as an exercise. That is why I said 'center(ish) above. It's not a big error by any means and the closer to the cell border the control is, the less the error is.

Modified to work with 2 buttons.

A side effect of this (no matter how many buttons)... it suffers from 'mouse creep' - click a button to display the message box a couple of times and you'll understand what I mean

[cmore]

Cytop, this is perfect and should get me where I need to go. I'm deconstructing slightly and should be able to put it together. Thank You very much will let you know as soon as it's I have it

[cmore]

I'm missing something. Not sure what just yet. So, because I have different functionality already associated with each button, I was going to skip creating classes (though I love this approach). So I was trying to just generate all the cursor positions for the buttons respectively and then on the related click can just go where it needs to.

Problem is my cursor #'s are very off not sure why. Doing some digging (likely because I'm mutilating the masterpiece you provided) So Close, having some error with the height. but width is looking classy.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[cmore]

I cannot get this to work consistently. Not sure if it's because I tried to combine procedures / functions, but I keep getting nonsensical values i.e. Y-coord = 24 or x-coord 75 off expected value

[cmore]

Ok There's an error when running the PointToScreenPixels methods. Not sure what it is, because it occurs / corrects itself without me changing anything in the code?

Please Login or Register  to view this content.

[cmore]

Ok So there's an error, that I don't know why / where. I'm running the code just for the window as below and I'm getting wildly different ScreenPointsX and Y respectively. Not sure why they're jumping around. I tried to move the cursor / activecell around to see if I can pinpoint anything explicit to no avail.

Please Login or Register  to view this content.

[cytop]

I realise your error is in the calculations but these work fine for me so I can only assume you missed something when moving the code to the individual controls so perhaps this might help you to retain the class.

Maybe I complicated things with the multiple instances of the class. It can be difficult to track down errors when someone else changes code you've written (not a criticism - I know how the code worked and it can take a little time to understand the new logic), so I took the easy way out - another sample workbook.

This only uses one instance of the class and works with both Forms buttons and ActiveX controls (or anything you can relate to a cell - I got it to center the mouse over a range of cells but if ever there was pointless functionality...)

ToggleButton as before - to turn on/off mouse centering but now it sets a property in the class.

There are 2 button (1 Forms, 1 ActiveX) at the left of the sheet. These both call the same procedure and are mainly there to show how different controls are handled. On second thoughts, these are redundant - but the procedure has full comments so I left it in rather than commenting the individual procedures.

Same 2 type of buttons on the right, these call their own procedures. The code for these buttons is

Please Login or Register  to view this content.

There's not too much to explain there as more of the code has been isolated in the class to simplify things in the code module - there's only 4 lines for code to handle the mouse in each procedure and togglebutton just sets a flag in the class, so only 1 line of code in that now.

The limitation of the 1 class approach is that you cannot click, for example, the Forms button on the right and then click the ActiveX button on the right and then center the mouse over the Forms button. Previously each button had its own instance of the class and you could center over that button just by calling the Center procedure in the correct instance of the class. That may or may not be what you want, but I really have no idea how this is to be used, so am guessing here.

[cmore]

Sorry to mess up your lovliness. Trying to adapt it.....emphasis on the word trying. I'll use the classes. The only issue / question is when I try to use this code more extensively? For instance, I want Button 1 to go to button 2 after click, button 2 to go to itself after click, toggle to go to button 2 after click and an exception for Button 2 when it's in a certain row to go to Button 1 instead of itself.

And maybe a really stupid question, but on button click, since they're activex buttons / toggles, will they just run through their own code before running the clicform sub or vice versa?

Finally, yes. Going to try and just run with the 'class' and see if I can nail it. It's weird though how the .PointsToScreenPixels is behaving, it's going negative without and doing strange things without any other edits to it.

[cytop]

toggle to go to button 2 after click

Do you mean when clicked down or Up? Otherwise why have a Toggle?

_______________________________


Oh well - hacked about a little. See the 2nd sheet. How the class works has also been changed a little.

Togglebutton - if clicked Down moves the mouse to Button2. Clicking up moves to button 1
CommandButton1 - Moves mouse to button2
CommandButton2 - if in row 8 then returns mouse to itself after clicking, otherwise returns mouse to Button1
CommandButton3 - Move CommandButton2 from Row 8 to Row 21 and vice versa and positions mouse over Button 2

All just as an example - if I get a chance I'll have a look later and see if I can track down your error.

[cmore]

Sorry, when Click down, one set of time starts, but I also want to move to Button 1 or Button 2 depending on the section type. And when those are clicked they start a diff. set of time constraints. When Button 1 is pushed it needs to go to button 2 and Button 2 goes to itself unless one of 2 other conditions. I just think computers are better than humans if you speak softly and correctly to computers. avoid human error.

Ahh I think I'll get there with this 2nd Sheet. I just need to isolate some craziness.

[cytop]

And my problem is I don't know the process logic - what moves where and when.

So the class was modifed to just center the mouse over a control, or shape. You do your Ifs and Buts in code, determine the target and just call CenterMouse

What's not in that is a call to center over a forms control, but you just call it passing the containing shape like before

Please Login or Register  to view this content.

The False parameter is just an optional parameter to show a MsgBox which I used to 'simulate' your code processing. You should change that to False

Please Login or Register  to view this content.

and you can forget about it.

PS: Your .SPEAK scared the bejesus out of the other half... and had me guessing for a minute or 2!

[cmore]

Thank you very much! Seriously, was scouring the web, I'm still trying to understand syntax for Class declarations, API's and so many of the things that are actually impressive. So I've been struggling with a few of these concepts, so again your help / insight very much appreciated!

[cmore]

Sir. Cytop, so after some digging, deconstructing, crying and more crying, I'm starting to think my workbook has some kind of error/bug in it that's messing with me.

I stripped your beautiful file to bare bones, no classes running the same code one tab with same functionality for the activeX buttons. Works like a charmless worker that works. The moment I copy the same exact code to the other workbook and switch the reference for the copied procedure to the SectionToggle button, the windows.pointtopixels goes crazy. Not sure, why, it's just sad disheartening and emasculating


Thanks for any addl. thoughts. This thing is driving me crazy, especially when you've shown me such beauty is possible.




**Sorry about the speak. Let me cut it off in this workbook. Thanks again for your help sir and by the way this workbook of yours is a mini masterpiece!

[cytop]

May I refer you to post #16...?

...will have you tearing your hair out

I'll download those and have a look in a little while.

[cmore]

you should play the lottery because you're seeing the future with no time machine.

[cytop]

There's only 1 outcome when working with screen positions in Excel - Alopecia

I presume you are looking at the big buttons. If so, we'll start simply, what happens when you click 'S'?

If not then please put me right and explain.

[cmore]

Indeed and agreed. So S, I attached to the Sub below, but haven't connected it functionally. But when I run the code the cursor finds its way to the very top left hand corner of the screen. But if I run essentially the same Sub on the classless workbook with CommandButton2, I find Nirvana.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[cytop]

OK - I still have no idea how to use this (as a user) so just added 2 test buttons - Center Over 'S' & Center Over '?'.

Clicking those moves the mouse pointer to the center of whichever control.

All the code to do with the centering is in its own module (modCenter). You position the mouse over a control simply by calling the CenterOver sub passing the shape to center over

Please Login or Register  to view this content.

If you want to move this to another workbook, just copy the module modCenter.

You really should remove any redundant code to do with this - I did try to go through it but I simply don't have the time, which is mainly why I haven't tried to figure out where you're going wrong.

Just noticed a little glitch - if the control you are centering over is not in the visible range then the mouse is moved to the screen location where it would be... even outside the Excel window. Understandable, I suppose as it uses screen coordinations, not the Excel window coordinates. It can be resolved but probably not worth spending time on sometime that might only happen once in a blue moon.

[cmore]

Gotcha. Thanks a bunch. Taking a gander now.

[cmore]

I think there's something going on with the buttons. I just added the "caller" in the Setup Button and things go weird and offscreen. I'm going to re-write the buttons and hopefully not the workbook otherwise I'll need that propecia subscription

Please Login or Register  to view this content.

[cmore]

Aha, I found some strangeness, which is what I've been looking at a while now. The issue is the location of the buttons and it's probably because of the wonderful joy of freeze panes, because the pointtopixels is a pane property apparently. So if you slide commandbutton21 over across the FP horizon, the cursor will go to no man's land

[cmore]

Yep. Freeze Panes. I've spent a day struggling with copy paste and annoying you and uglyfying some mighty beautiful code because of freeze panes. Unfortunately, I have no idea what the math is on this, I'm sure there might be some documentation on it somewhere, I'm wondering if I can cheat and unfreeze pains, get my coordinates and then freeze panes back to where they were?.............the inquiry of a simple mind.

[cytop]

Ahhh - never even noticed the freeze panes.

This'll be fun,. give me a little time

[cmore]

It works, it all works, the problem is freeze panes. Slide the button over to the right past column K, then click and the world becomes an unfriendly place. I unfroze and everything is fine as kate on her wedding day

[cmore]

https://social.msdn.microsoft.com/Fo...forum=exceldev

My initial posting is correct. ActiveWindow.PointsToScreenPixelsX is broken, and requires a parameter in pixels, not points. Which requires all the stuffing around mentioned above. And even more to get split/freeze windows to work.

But of course, ActiveWindow.ActivePane.PointsToScreenPixelsX is quite different from ActiveWindow.PointsToScreenPixelsX. The Pane version does in fact work as advertised, and takes a points parameter.

However, for freeze panes, ActiveWindow.ActivePane.Index is always 4 (or 2) regardless of where the cursor is. So you need to search for the active pane using VisibleRange.

And then it all works.

Provided one has ensured Application.ScreenUpdating is True.

And so was wasted most of my day today. Help this saves someone else the grief.

Anthony

Anthony

[cytop]

I'll go and glue hair back in...Well done. I think I got myself in a rut as well and couldn't see the forest for the trees.

But... I am sure there is another way. Something to ponder and twiddle instead of trying to finish the jigsaw.

[cmore]

Exactly! Thanks so much for your help. I cannot even decipher what he means by the Pains.Points property right now. I love microsoft apparently they're supported by the hairless lobby

[cmore]

Also if no one has told you today, you are indeed a gentleman and a scholar!