Windows7 API to detect cursor position on extension screen (to open userform at the screen coordinates of 'mouse clicked)

The attached contains API code that gets the position of mouse click (when clicking Active X button on sheet1).
When that button is clicked, Userform1 appears over the button, when xlApp  is located on the main laptop (not extension) screen.

code sourse:

My laptop has an extension screen that is wider and deeper than the laptop screen

When xlApp  is launched on the laptop screen ans then dragged onto the extension screen, the horizontal positioning of the userForm (via mPos.left in Public Function convertMouseToForm) ceases to track mouse click, over the right hand half of the extension screen. In contrast, mPos.Top is detected accurately on either screen.

When I make the extension screen the Main screen (via Screen resolution), then mPos.left accurately tracks mouse click over both screens.

How do I detect the main screen and correct the calculation of mPos.left when the small laptop screen is assigned as main screen?

APIs are not a strength of mine.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi Kelvin,

Just to confirm: by "extension screen" do you mean just an external monitor that you are extending your MS-Windows desktop across?

Does it matter precisely where on the button the click occurs in order to position the UserForm on that exact point of contact, or do you just wish to display the form over the button (wherever it is displayed, on the main screen, the secondary screen or, perhaps, split over the cusp of the two)?

Thank you for providing the workbook above, but it does not do what I expected.  For me, when the button is clicked "UserForm1" is displayed in the centre (vertically & horizontally-centred) of my (laptop) screen.

Was that intentional, or does the code do something differently for you?

I am presuming the value of the Left co-ordinate of the resultant position in your set-up (that I cannot replicate due to lack of hardware) is going to be offset by a number influenced by the width of either the main screen, or the 'extension' screen.

It may be possible to adjust this programmatically if you can confirm what the difference is between what you expect, & what is returned, within your existing code statements/API usage.
Kelvin4Author Commented:
Hi FanPages
Thanks for responding.

1. Yes, by "extension screen" I mean just an external monitor that i extend my MS-Windows desktop across.

2. PRECISION.. 'Does it matter precisely where on the button the click occurs in order to position the UserForm on that exact point of contact?'  Pixel precision is not required, I am trying to deliver the UserForm to where the User was already  looking, ie the position of  the button he had just clicked.

3. 'For me, when the button is clicked "UserForm1" is displayed in the centre (vertically & horizontally-centred) of my (laptop) screen.'  Crickey!: that has not happened for me! So...

I downloaded the file that you downloaded from the EE site, & unplugged the extension screen's data cable from my laptop, so it worked only off its own screen.

-- When I moved the Excel window around the laptop screen, and clicked the ActiveX button, the UserForm always tracked the button.

I then re-connected the extension screen, and dragged the xl window 'just across' onto the adjacent part of the extension screen, then:
(a) the FIRST click of the ActiveX button launched the UserForm back on the laptop screen.
(b) the next click launched UserForm1 over the ActiveX button (now on the adjacent part of the extension screen).
(c) as I moved the xl window further into the extension screen (away from the laptop screen), so the UserForm launched on the extension screen , but on the lap-top side of the active X button.
(d) As I moved the xl window further onto the extension screen, so UserForm1 stopped moving toward the xl window , and launched abour 25% of the way into the extension screen, irrespective of how much further the xl window was postioned into the extension screen.

Finally, when I move the xl window back and forth across the border between the laptop and extn screens, so the first time the window moveed onto the new screen, the UserForm (when triggered) did NOT cover the active X button. Then when I re-clicked the the active X button WITHOUT having moved the xl window (ie a repeat click), then the userForm 'caught up' and positioned itself over the button, as required.

4. You refer the the issue that the active X button might be on the interface between the two screens. I had not though of that, and would expect the xl window to usually be on one screen or the other.

Sorry about the amt of text in reply.
I will try to respond to questions ASAP.

Thanks again


Kelvin4Author Commented:
Did you try re-launching the xl file, to see if you recovered at least some of the functionality I get?  Are you windows7? Puzzling..
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

[ fanpages ]IT Services ConsultantCommented:

No need to apologise :)

Your extended response helped me understand what you are trying to achieve.

Firstly, when I replied above, I only used my MS-Excel application (MS-Excel 2013) in an MS-Windows 8.1 environment, maximized:

Maximized (1920x1080 screen resolution)
Reducing the application window to Height 480 x Width 640, & placing in any of the four corners of my screen shows a different outcome:

H480W640 - Top Right of 1920x1080 screen
However, if you (only) wish to place the UserForm over the button, you could use the Left & Top positions of the Shape (CommandButton) with an offset of the Application Windows Left & Top position, without using any API calls.

Is this something we can look at (together), or can you investigate further (within your own environment) on your own?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
[ fanpages ]IT Services ConsultantCommented:
PS. I also see the "StartUpPosition" property of UserForm1 is set to "1 - CenterOwner":

Changing this to "0 - Manual" may yield better results for you.
[ fanpages ]IT Services ConsultantCommented:
Setting this Property to "0 - Manual", & changing the CommandButton1_Click() event code to what is shown below, produces better results (for me):

Private Sub CommandButton1_Click()

'    Dim mPos As tCursor
'    mPos = convertMouseToForm()

'    UserForm1.Show
'    UserForm1.left = mPos.left
' =

  UserForm1.left = Application.left + Me.Shapes("CommandButton1").left + (UserForm1.Width / 2) = + Me.Shapes("CommandButton1").top + (UserForm1.Height / 2)
End Sub

Open in new window

I do not know where is the "best" position; whether this is directly (centre; horizontally/vertically) over the button/click, or offset a little, as I have it above.  I was concerned about completely covered the button with the Form.  This may not be a concern for you, though.

Additionally, I would be tempted to look at the CommandButton1_MouseDown() event as an alternate approach.

This event provides an 'X' & 'Y' co-ordinate of the hover position on the Command Button control at the point the "Down" (click) is actioned.

This may prove useful if you want (more) precision to the positioning.
[ fanpages ]IT Services ConsultantCommented:
With the revised code, maximized...

Maximized - Changed code
Revised code; application window height 480/width 640 in a random location on my screen...

H480W640 - Changed code
Kelvin4Author Commented:
Hi fanPages
Just to say - I'm making progress and checking some things out, then I'll report back.

For any other potential EE Expert commentators:
fanPages is guiding me, and we are progressing, thanks!

[ fanpages ]IT Services ConsultantCommented:
Thanks for the update, Kelvin.

Good luck :)
Kelvin4Author Commented:
hi fanPages.
Your comments were most helpful.
I agree that API and mPos.Left were ineffective.
My original test was not a discriminatory as I had thought (so severe note to self!!)

Your method of using shape coordinates for the activeXcmd button works well, and is directly transferable to a similar issue I have with MS Visio.
Many thanks
Kelvin4Author Commented:
Very rapid and penetrating comments, backed up by the Expert's careful critique of code in my attached file.
I re-evaluated that code, realised that I had misinterpreted the outcome, and adopted the Expert's simpler solution.
Saved much time, and can also apply the solution to another application.

[ fanpages ]IT Services ConsultantCommented:
Hi Kelvin,

Glad I could help, & that you can re-use the approach elsewhere.

I also appreciate your updates as you progressed.  Very kind comments.  Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.