拦截共享工作簿上的“粘贴”事件并运行宏

I would like to run a certain procedure (macro) when a user is trying to paste an image on a sheet of my workbook.

当用户尝试将图像粘贴到工作簿的工作表上时,我想运行某个过程(宏)。

If the user would be just pasting a simple text into the sheet of a non-shared workbook, then I could easily detect the event via the Workbook_SheetChange of the Worksheet_Change (with some tricks to understand that it was a "Paste" event and not a simple text manual typing).

如果用户只是将一个简单的文本粘贴到非共享工作簿的表单中,那么我可以通过Worksheet_Change的Workbook_SheetChange轻松检测到该事件(有一些技巧可以理解它是一个“粘贴”事件而不是简单的文字手动打字)。

However, here the complexity is added by two factors:

但是,这里的复杂性有两个因素:

If I paste an image, the event Worksheet_Change is not triggered even when the workbook is not shared;

如果我粘贴图像,即使未共享工作簿,也不会触发事件Worksheet_Change;

In shared workbooks is impossible to paste images. Hence, Excel's native's code seems to stop the action even before the event could be detected.

在共享工作簿中无法粘贴图像。因此,Excel的本机代码似乎甚至在检测到事件之前就停止了操作。

Does anyone have any idea (even including Windows API to intercept clipboard actions)?

有没有人有任何想法(甚至包括Windows API拦截剪贴板操作)?

1 个解决方案

#1

If it can help anyone, I've finally solved the above with a "not very nice but working solution":

如果它可以帮助任何人,我终于用“不太好但工作正常的解决方案”解决了上述问题:

On Workbook_Open, I've added a listener for the Ctrl+v key that will not perform the usual paste but rather will call a separate macro:

在Workbook_Open上,我为Ctrl + v键添加了一个不会执行常用粘贴的侦听器,而是调用一个单独的宏:

Application.OnKey "^{v}", "mySpecialPaste"

The code of mySpecialPaste being:

mySpecialPaste的代码是:

MakeExclusive '<-- procedure to make the workbook exclusive
Application.OnKey "^{v}" '<-- remove the "Ctrl+V" link to the macro
Application.SendKeys "^{v}" '<-- send a Ctrl+V to perform the paste
Application.OnKey "^{v}", "mySpecialPaste" '<-- re-link the Ctrl+V to this macro
MakeShared '<-- make the workbook shared once again

Of course it's not an elegant solution since it relies on the fact that the user performs the paste of the picture using the Ctrl+V key and not, for example, a right click / paste. Plus, this will be triggered on any Ctrl+V (even if the user is just pasting normal text, which is a normal action even on a shared workbook). Finally, it relies on the SendKeys which is well known to be not very nice (not reliable, not stable).

当然,这不是一个优雅的解决方案,因为它依赖于用户使用Ctrl + V键执行图片粘贴的事实,而不是例如右击/粘贴。此外,这将在任何Ctrl + V上触发(即使用户只是粘贴普通文本,即使在共享工作簿上也是正常操作)。最后,它依赖于众所周知的不太好的SendKeys(不可靠,不稳定)。

However, it was satisfying for my specific use case so I've thought about sharing it as an answer since none has thought to a better solution so far.

但是,它对我的特定用例感到满意,所以我考虑将其作为答案分享,因为到目前为止还没有人想到更好的解决方案。

#1