剪贴板“复制"自动调整VBA按钮时选择消失了吗? [英] Clipboard "copy" selection gone when autosizing VBA button?

查看:102
本文介绍了剪贴板“复制"自动调整VBA按钮时选择消失了吗?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我很困惑,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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆