vba 如何禁用特定工作簿的单元格拖放？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25387602/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to disable Cell Drag and Drop for a specific workbook?
提问by Hunter
I have a spreadsheet where users input information into column A that is used in formulas in column B. B1 uses the information in A1, B2 uses in the information in A2, etc.
我有一个电子表格，其中用户将信息输入到 A 列中，该列用于 B 列中的公式。 B1 使用 A1 中的信息，B2 使用 A2 中的信息等。
The sheet is "protected" to only allow users to select and change unlocked cells in column A. They should not be able to change the formatting or structure of the sheet. However, even when the sheet is protected, they are able to "move" cell contents by clicking on the frame of cell and dragging it onto another cell.
工作表受到“保护”，仅允许用户选择和更改 A 列中未锁定的单元格。他们不应更改工作表的格式或结构。但是，即使工作表受到保护，他们也可以通过单击单元格框架并将其拖到另一个单元格上来“移动”单元格内容。
If they move A1 onto A2, B1 is now using the information in A2, and B2 gives a reference error.
如果他们将 A1 移动到 A2，B1 现在正在使用 A2 中的信息，并且 B2 给出参考错误。
I would like that, when a user "moves" a cell, a macro is fired that will undo the move.
我希望，当用户“移动”一个单元格时，会触发一个宏来撤消移动。
How can I programmatically detect when a cell is moved or is there a different solution?
如何以编程方式检测单元格何时移动或是否有不同的解决方案？
回答by David Zemens
Does anyone know of a way to detect when a cell is moved programmatically, or have a different solution to my issue? Other than Microsoft not overlooking this issue with sheet protection :D
有没有人知道一种检测单元格何时以编程方式移动的方法，或者对我的问题有不同的解决方案？除了微软没有忽视工作表保护的这个问题：D
I have another solution.
我有另一个解决方案。
Disable the drag-and-drop functionality in this workbook. The tricky part is that this is an Application-level setting, so you need to toggle it Enabled/Disabled based on which workbook is Active.
禁用此工作簿中的拖放功能。棘手的部分是这是一个应用程序级别的设置，因此您需要根据哪个工作簿处于活动状态将其切换为启用/禁用。
This is not exactly beginnerlevel VBA but I have documented it in THIS ANSWERand provided a link to an XLSM file containing all of the sample code & modules.
这不完全是初学者级别的 VBA，但我在这个答案中记录了它，并提供了一个指向包含所有示例代码和模块的 XLSM 文件的链接。
回答by Steve S
Realize this is an old post, but I came across it looking for the same thing so I'm sharing an alternate way to address this that I subsequently came up with.
意识到这是一个旧帖子，但我在寻找相同的东西时遇到了它，所以我分享了一种替代方法来解决这个问题，我后来想出了这个方法。
Use the OFFSETfunction to reference the cells in your formula so that you don't have to worry about the drag-and-drop moving the referenced cells. In the example, B1 references the value in A1, so in B1 use OFFSET(B1,0,-1)to reference A1.
使用该OFFSET函数来引用公式中的单元格，这样您就不必担心拖放移动引用的单元格。在示例中，B1 引用了 A1 中的值，因此在 B1 中使用OFFSET(B1,0,-1)引用 A1。
