PDA

View Full Version : Looking for functions: IsUserPressingAMouseButton / IsUserPressingAKey



fishchoke
11-29-2011, 04:25 PM
Howdy all,

Please forgive me I am not a professional programmer, just an engineer trying to hack my way through some excel macros.

I am making simple macros for my department that run directly out of spreadsheets in Excel 2007 on Windows XP. These macros loop through input lists (which are typed into the spreadsheet by the user) and perform various tasks for each item on the list. Mainly these macros activate a separate BPCS client program, and using sendkeys, copy-paste data to and from the excel spreadsheet.

While sendkeys activities are occuring, it is critical that the user does not start typing or clicking things with the mouse. So what I am looking for are functions that indicate if the user is pressing a key or clicking a mouse button while the macro is running. Something like this:

sub myroutine
for i = 1 to 9999
'do the work
x = cells(i,1).value : y = x * 2 : cells(i,2).value = y
'check to see if user is pressing or has pressed a key
if IsUserPressingAKey = true then
'stop macro execution cold
end
endif
'check to see if user is pressing or has pressed a mousebutton
if IsUserPressingAMouseButton = true then
'stop macro execution cold
end
endif
next i
end sub

public function IsUserPressingAKey as boolean
????
end function

public function IsUserPressingAMouseButton as boolean
????
end function

I have already explored onkey, doevents, userforms, etc., but not with much success. Does anyone have any example functions similar to what I'm looking for, leads, suggestions?

Thanks very much,
Nate

mikerickson
11-29-2011, 05:02 PM
If the user is using a userform, the KeyDown and MouseDown events will test for that.
On a worksheet, there are techniques for key down that are perilous for even experienced progammers.

During a macro, pressing a control will have no effect until the macro is finished running.

If you routine is driven by many SendKeys, its possible that there are better ways that are more robust.