Results 1 to 3 of 3
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Finding out if a macro was called by a key (ALL)

    Is there any way that when a macro is run, I can find out how it was called.
    In particular I want to know if it was called by a Key Press such as SHIFT CTRL P,
    or whether it was by a button.
    I have had a look at Application.Caller, but this returns Error for both Tools-Macro-Macros Run method and Shortcut key press. I assume because it make no distinction between them.

    If it a button it returns String, etc.
    I am guessing the answer is NO unless either there is a hidden property I cannot find out about, or an API routine that I do not know about.
    Anyone got any ideas?
    Andrew

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding out if a macro was called by a key (ALL)

    If a macro is called by clicking a toolbar button, that toolbar button is returned by CommandBars.ActionControl; otherwise CommandBars.ActionControl is Nothing.
    I don't think Excel distinguishes between starting a macro by selecting Tools | Macro | Macros and by pressing a keyboard shortcut.

    You could create a macro A that sets some kind of flag, then calls another macro B.
    Assign the keyboard shortcut to A instead of to B.
    This way, the flag would signal whether B was called directly or (via A) by the keyboard shortcut.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding out if a macro was called by a key (ALL)

    You could detect whether the shift key is depressed or not. See a simple routine at this page:
    http://www.jkp-ads.com/articles/workbookopenbug.htm Look for the routien called "ShiftPressed" (note that it needs the API declaration immediately above it in the same example)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •