Results 1 to 3 of 3
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Cambridge, UK
    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 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
    Weert, Limburg, Netherlands
    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: 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
    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