Solved

How to create a macro to "click" on specific coordinates

Posted on 2014-02-27
22
663 Views
Last Modified: 2014-03-05
Hi All,

Is there a way to get the mouse to click on specific coordinates on a specific sheet and then use this as a macro?

thanks,
0
Comment
Question by:BostonBob
  • 8
  • 7
  • 4
  • +2
22 Comments
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39894106
HI,

What are you trying to do?

Regards
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39894107
Getting the exact mouse position is not easy. Getting the exact mouse position relative to the sheet coordinates is even more of a challenge. Telling the OS to click on a specific location is even more of a challenge.

What are you trying to do? Select a cell? Click a command button?

Kevin
0
 

Author Comment

by:BostonBob
ID: 39894143
I am trying to Click a command button.  I have been using this third party application and have tried everything to call the function  the regular old fashioned way and nothing works.   BUT....

If I manually click the button my problem is solved and I can put to rest this ENTIRE MONTHS hair pulling of trying to figure this out.

I've even had a couple of other vba experts take a run at it and they are coming up short scratching their heads trying to solve it the conventional way.

...so if i can figure out how to "click the button" and make THAT into a macro then that would be just great.

This would not be a pretty solution but I don't care.  I'm not the compute and if the computer likes it, at this point, I like it.

thanks!
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39894150
If you want to "click" a command button then just call the macro attached to the command button directly.

Kevin
0
 

Author Comment

by:BostonBob
ID: 39894162
Yes, we have tried that every which way possible.

There is just something "funky" about the third party vendor's software that isn't working.

PLEASE--

If you have an answer in respect to actually clicking the command button thru a macro that would be best.  Really, we've spent a month on it, all of us know how to call a procedure, but none of us can figure it out.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39894167
I'm confused. You want to click a button on a worksheet from a VBA macro, right? That command button is tied to a macro, right? Call the macro directly. It really is that simple.

I'm sure I'm missing something here.

Perhaps this command button is in another application? Perhaps the macro is not VBA but something else in another application?

And what does this third party application have to do with anything?

Kevin
0
 

Author Comment

by:BostonBob
ID: 39894181
I'm confused too. That's why we have been trying to fix the thing for over a month.

What the third party application has to do with it not working we don't know--if we did we would not need to record little "mouse clicks" as a last resort nor explain ourselves here about why we can't call a macro.  

The marco is on the same sheet where we are trying to do work.  It should be simple, but it is not.  

I have other programmers working on it with all kinds of awards and what not and they cannot figure it out.  There is a glitch in the third party software and that's that.

Now, please, may I have a "click the mouse" macro solution?
0
 

Author Comment

by:BostonBob
ID: 39894192
It's late, I'm super frustrated.  Don't mean to sound like a grouch.

It's just that we've been working on this thing and we are all laughing at one another 'cause none of us can figure it out.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39894194
Here is what I know:

You have a workbook. In this workbook is a worksheet. The worksheet has a command button on that, when clicked, calls a macro. Am I correct so far?

If you were to successfully generate a mouse click event over the command button what Excel will do is generate a button click event which will execute the macro connected to the command button.

Now I'm not the sharpest tack in the box but I do know one absolute truth: calling that macro directly will produce the exact same result as generating a mouse click event over the command button.

So, unless part or all of my assumptions are incorrect, this is your solution. If any of my assumptions are incorrect, please correct them so that I can change my focus and help you solve the real problem and not the problem which I am assuming you have.

This:

"Is there a way to get the mouse to click on specific coordinates on a specific sheet and then use this as a macro?"

Does not tell me anything that gives me any reason to change my assumptions.

How about this for a question: what process/macro/application do you want to generate the mouse click?

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39894195
Oh, you're sounding like a grouch all right. But that's OK because I work with grouchy people all day ;-)

Kevin
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39894197
Hi,

Just to be sure

pls try this code to see whât macro is fired when you click the command

Sub macro()

For Each shp In ActiveSheet.Shapes
MsgBox shp.Name & ": " & shp.OnAction
Next
End Sub

Open in new window

Regards
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39894201
Hmmm...

Try the code in what? An empty workbook? It won't do anything since there are no shapes. Did you think you posted a workbook above?

Also, you have not yet described this "third party application" and what it has to do with this problem other than it's presence seems to be causing some undesired behavior.

By the way, I've been doing this stuff for years and can make Excel do just about anything. I've written code that interfaces to the Windows API. I've made Excel do things that amaze and astound. I'm a Microsoft MVP. I'm trained in software engineering. I started coding on an IBM mainframe in assembly language. If I can't figure this out I doubt anyone can.

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39894204
Oh, sorry Rgonzo1971, I thought you were the OP for a second :-)

Kevin
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39894207
@zorvek
Sorry Kevin just a fellow trying to help

@BostonBob
pls try this  code when the activesheet is the notorious one
Regards
0
 

Author Comment

by:BostonBob
ID: 39894242
I put this code into the sheet in question and there was no pop-up.

Sub macro()

For Each shp In ActiveSheet.Shapes
MsgBox shp.Name & ": " & shp.OnAction
Next
End Sub

I put the code into the macro that cannot be called. Nothing popped up.

???
0
 

Author Comment

by:BostonBob
ID: 39894245
How do we hire you guys directly?
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39894273
I suppose when you right Click your button you do not see assign macro?
0
 

Author Comment

by:BostonBob
ID: 39894300
I do see it. First time.  What does this thing do other than the obvious.  When I hit the "assign" and OK i go back to the list and cannot find it.  Gotta google this....
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39894312
We are not allowed to solicit work on Experts Exchange. But some of us do include contact information in our profiles. Look there to see how you can contact us offline.

The code that was posted by Rgonzo1971 lists any shape objects and the macros that they call. Since nothing was displayed then we can only assume that there are no command buttons on the worksheet.

What this means is that we don't really know anything about your problem and thus will not be able to assist until we get more information.

Questions:

Are there or are there not Forms or ActiveX command buttons on your worksheet?

When you click the command button, what does it do? Does it call a macro in the workbook's VBA project?

From where do you want to generate this mouse click? From VBA in the workbook? Or from some other application?

If from some other application, then what application? What does this application allow you to do? Does it have scripting functionality? What other ways does the scripting language use to communicate with other applications? Can you send a key press sequence? Can you write a file? Can you instantiate an OLE application object?

Kevin
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39894581
I got this code for the following website

http://excelhelphq.com/how-to-move-and-click-the-mouse-in-vba

You can call singleclick. Remember to change the coordinates within that sub or assign a variable or...whatever

Public Declare Function SetCursorPos Lib "user32" (ByVal x As Long, ByVal y As Long) As Long
Public Declare Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)
Public Const MOUSEEVENTF_LEFTDOWN = &H2
Public Const MOUSEEVENTF_LEFTUP = &H4
Public Const MOUSEEVENTF_RIGHTDOWN As Long = &H8
Public Const MOUSEEVENTF_RIGHTUP As Long = &H10

Private Sub SingleClick()
  SetCursorPos 100, 100 'x and y position
  mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
  mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
End Sub

Private Sub DoubleClick()
  'Double click as a quick series of two clicks
  SetCursorPos 100, 100 'x and y position
  mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
  mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
  mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
  mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
End Sub

Private Sub RightClick()
  'Right click
  SetCursorPos 200, 200 'x and y position
  mouse_event MOUSEEVENTF_RIGHTDOWN, 0, 0, 0, 0
  mouse_event MOUSEEVENTF_RIGHTUP, 0, 0, 0, 0
End Sub

Open in new window

0
 
LVL 23

Accepted Solution

by:
Eirman earned 500 total points
ID: 39894631
Typeitin from http://www.wavget.com/typeitin.html at first look, seems to be a basic program, but you can do a lot with it.

It has Variables, If - then, Wait for pixel colour, while loops, Command Line Options and much more.

It has saved me hundreds of hours over the years, in many different programs and it may help you.

Here is a sample of code that saves me hours when making collages with Lumapix..
{Delay 200}{Activate LumaPix*}{Delay 300}

{Var1 List Title: New ....,EMPTY LAST,1680-01,1680-02,1680-03,1680-05,=======,1920-01,1920-02,1920-03,1920-05,1920-10,1920-15,2 + 2 + 3 LARGE,2 + 2 + 3 MEDIUM,2 + 2 + 3 SMALL,BIG THUMBS}
{Goto {Var1}}
{Label EMPTY LAST}
{MouseMove 85,55}{MouseRight}{*** Create TAB ***}
{Delay 150}{MouseMove 72,75}{MouseRight}{*** Images TAB ***}
{Delay 150}{MouseMove 43,103}{MouseRight}{*** Delete ALL Images ***}
{Delay 300}{MouseMove 21,78}{MouseRight}{*** Pages TAB ***}
{Delay 300}{MouseMove 207,94}{MouseRight}{*** NEW ***}
{Delay 300}{MouseMove 884,474}{MouseRight}{*** LAST TEMPLATE ***}
{Delay 500}{MouseMove 844,560}{MouseRight}{*** NO ***}
{MouseMove 85,55}{MouseRight}{*** Create TAB ***}
{Delay 300}{MouseMove 207,94}{MouseRight}{*** NEW ***}
{Delay 300}{MouseMove 884,474}{MouseRight}{*** LAST TEMPLATE ***}
{Delay 500}{MouseMove 844,560}{MouseRight}{*** NO ***}
{Exit}

{Label 1680-01}
{MouseMove 85,55}{MouseRight}{*** Create TAB ***}
{Delay 150}{MouseMove 72,75}{MouseRight}{*** Images TAB ***}
{Delay 150}{MouseMove 43,103}{MouseRight}{*** Delete ALL Images ***}
{Delay 300}{MouseMove 21,78}{MouseRight}{*** Pages TAB ***}
{Delay 300}{MouseMove 207,94}{MouseRight}{*** NEW ***}
{Delay 300}{MouseMove 892,542}{MouseRight}{*** OPEN EXISTING TEMPLATE ***}
{Delay 300}{MouseMove 738,560}{MouseRight}{Delay 50}{Repeat 55,{Bksp}}{*** CLEAR NAME BOX ***}
{Delay 100}1680-01.Collage{*** CHOOSE "1680-01" ***}
{Delay 400}{MouseMove 1001,708}{MouseRight}{*** OK ***}
{Delay 500}{MouseMove 844,560}{MouseRight}{*** NO ***}
{Delay 500}{MouseMove 364,91}{MouseRight}{*** ADD NEW FILES BUTTON***}
{Delay 500}{MouseMove 687,389}{MouseRight}{Delay 600}{MouseRight}{Delay 600}{Ctrl c}{*** FIRST FILE & COPY ***}
{Delay 500}{MouseMove 692,405}{MouseRight}{Delay 600}{Ctrl a}{*** SELECT ALL FILES ***}
{Delay 50}{MouseMove 1074,651}{MouseRight}{*** OK - TO SELECT ALL FILES ***}
{Exit}

{Label 1680-02}

etc. etc.

   D E L E T E D

{Exit}

Open in new window

As you can see, there is plenty of moving the mouse to a precise location and clicking.
The whole operation can be assigned to  a shortcut key.
(The above is for left handed mouse usage).
0
 
LVL 23

Expert Comment

by:Eirman
ID: 39905967
Thanks for the points.

I use this free utility to get exact coordinates for "MouseMove" ....
http://www.softpedia.com/get/Desktop-Enhancements/Other-Desktop-Enhancements/M-S-Utility.shtml
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now