I have several Excel workbooks. Each one has a main worksheet with a unique title and four secondary worksheets with the same titles across all the workbooks.
I have a group of KM macros that work with these workbooks. Currently, I have assigned the same hot key trigger to all of the macros in the group and use the conflict palette to select the one I want at the time. But some of the macros are only applicable to the secondary worksheets so it kind of clutters things up to always have them there if I don't currently have one of those worksheets active.
I've been a KM user for several years but my usage has been rather simplistic. One of my goals this year is to step up my KM game and take more advantage of all it can do.
In that light, I'm wondering if there is a way I could only see the macros for the secondary sheets when one of them is active? I don't see any way to, say, run an AppleScript to determine if a macro should be active in a given context. But maybe I'm just not looking in the right place.
I'm envisioning something like this:
tell application "Microsoft Excel" to set activeTab to name of active sheet
return {"Secondary1", "Secondary2", "Secondary3", "Secondary4"} contains activeTab
and then depending on the return value of that script, the relevant macros would be active or not.
Probably not. Not unless the selection of the worksheet changes the window title (if it did, then you can use the window title as part of the macro group activation preferences).
Activation settings require thing that can be detected when they change with exceptionally low cost, generally events that Keyboard Maestro can detect. Running an AppleScript to detect a state would definitely not be viable - even if it wasn't going to be too expensive, how would Keyboard Maestro know when to run the AppleScript?
Excel workbook titles don't change their names according to which sheet is active, so there's no good way to automatically detect what sheet you've switched to.
Your script example won't work as is, but something like this will:
tell application "Microsoft Excel"
set activeTabName to name of active sheet
return {"Secondary1", "Secondary2", "Secondary3", "Secondary4"} contains activeTabName
end tell
You could create a macro that would run on demand with a hotkey trigger that would alter your macro landscape.
I suppose I was thinking of some kind of filtering process that would sit between the hot key trigger and the display of the palette rather than something that would detect when the tab had changed. So all the same macros would still be part of the set of macros available under the same hot key, but you would have an opportunity to say yea or nay to showing each one before the palette appears.
Hmm, so perhaps just one macro triggered by the hot key and, instead of relying on the conflict palette to present the available macros, I could use the Execute an AppleScript action to test which tab is active and then an If Then Else condition with a different Show Palette of Macros action in each branch to present just the macros I want to see. That might work. Thanks!
I'm curious why you say that. It seems to work fine when I run it in an Execute an AppleScript action. I get back "true" or "false" depending on which tab I have active in Excel.
You can do this by using a macro with a hot key trigger, and then use an AppleScript (or whatever) to determine the state, and then use one of two Show Palette of Macros actions.
I don’t 100% follow what you’re all taking about here, but I had a thought that might be relevant.
I use the hot keys Option-Right Arrow and Option-Left Arrow to switch worksheets in Excel. It would not be difficult to also co-opt that for running a macro simultaneously.
Careful with that. ⌥→ and ⌥← switch worksheets when you aren't in an active text field. When you are -- you've double-clicked a cell or are using the formula bar -- those keystrokes have the normal "forward/back a word" behaviour, and you'll lose that if you override them with KM triggers.
Of course, if you never move through text in Excel that way then it doesn't matter!