剪贴板“复制"自动调整VBA按钮时选择消失了吗? [英] Clipboard "copy" selection gone when autosizing VBA button?
问题描述
我很困惑,Excel在创建自动大小按钮时没有(明显的)理由就放弃了剪贴板的复制"选择.
考虑这个简单的选择更改处理程序:
Consider this simple selection change handler:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Dim P As Button: Set P = ActiveSheet.Buttons.Add(1, 1, 100, 100)
End Sub
此简单操作会在每次单元格选择更改时在工作表的左上角创建一个哑按钮.
This simple creates a dumb button on the top left corner of the sheet on every cell selection change.
如果在任何单元格中按Ctrl-C(无论它是否为空),该单元格都会有一个漂亮的边框,表明如果选择了粘贴到其他位置,则将粘贴所选内容.
If you press Ctrl-C in any cell (no matter if it´s empty or not), the cell will have this nice border indicating that the selection is what will be pasted if you select paste elsewhere.
即使您在工作表上四处浏览,该边框也将保持可见.
That border will remain visible even if you navigate around on the sheet.
现在添加一行:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Dim P As Button: Set P = ActiveSheet.Buttons.Add(1, 1, 100, 100)
P.AutoSize = True
End Sub
这会使按钮自动调整大小.工作良好.但是从已知的角度来看,每次选择更改都会破坏剪贴板的复制"选择.
This makes the button(s) autosize themselves. Works fine. But from know on, every selection change will destroy the clipboard "copy" selection.
- 为什么?我可以防止这种情况还是解决该问题?
由Excel 10 14.0.7116.5000 32位:-O复制
Reproduced with Excel 10 14.0.7116.5000 32-bit :-O
推荐答案
如果您复制了内容,则需要先粘贴内容,然后再进行其他操作.这就是Excel的自然行为方式.
If you have copied content then you will need to paste it before anything else happens. This is how Excel behaves naturally.
因此,在这种情况下,您可以在自动调整按钮大小之前将内容粘贴到当前活动的单元格中.
So, in your event, you can Paste the content (to the currently active cell) before Auto-Sizing the button.
Dim P As Button
If Application.CutCopyMode = xlCopy Then
Me.Paste
End If
Set P = ActiveSheet.Buttons.Add(1, 1, 100, 100)
'Application.EnableEvents = False
P.AutoSize = True
'Application.EnableEvents = True
EnableEvents
在这里是不必要的,但是我已经包含了它,以指示您如何防止事件再次触发.您可能需要在某个时候使用它.
EnableEvents
is unnecessary here, but I've included it to indicate how you might prevent an event from triggering a second time. You probably need to work with it at some point.
这篇关于剪贴板“复制"自动调整VBA按钮时选择消失了吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!