excel - VB在多页中右键单击复制/粘贴

Question

让我以我是自学成才的事实作为我的问题的开头，所以请提供尽可能详细的信息，如果我需要您以不同的方式或多次解释，请耐心等待。

我使用 Microsoft Visual Basic 7.0 为我的团队创建了一个符号/电子邮件生成工具。我收到的唯一提示是他们中的许多人不习惯热键，所以他们依赖于使用鼠标，但右键单击不起作用。当他们使用右键单击时，我能够找到创建用于复制和粘贴的弹出窗口的代码，并且它在主窗体本身的几个文本框上工作得很好，但是它不适用于大多数文本框，因为他们在多页中。

有谁知道如何修改下面的代码以适用于多页上的文本框？此外，在提出建议之前，我确实考虑过将所有内容移出多页的想法，但是这种格式是最简单的，因为他们需要随时发送多个阶段和类型的笔记/电子邮件，所以有他们只需单击即可使用的选项卡是我能够创建的最用户友好的，并且他们都同意。

提前谢谢大家!

表单中的代码:

Dim cBar As clsBar

Private Sub UserForm_Initialize()

    On Error GoTo Whoa
    Application.EnableEvents = False

    Set cBar = New clsBar
    cBar.Initialize Me

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue

End Sub

类模块中的代码:

Option Explicit

'Popup objects
Private cmdBar As CommandBar
Private WithEvents cmdCopyButton As CommandBarButton
Private WithEvents cmdPasteButton As CommandBarButton

'Useform to use
Private fmUserform As Object

'Control array of textbox
Private colControls As Collection

'Textbox Control
Private WithEvents tbControl As MSForms.TextBox
'Adds all the textbox in the userform to use the popup bar
Sub Initialize(ByVal UF As Object)
    Dim Ctl As MSForms.Control
    Dim cBar As clsBar
    For Each Ctl In UF.Controls
        If TypeName(Ctl) = "TextBox" Then

            'Check if we have initialized the control array
            If colControls Is Nothing Then
                Set colControls = New Collection
                Set fmUserform = UF
                'Create the popup
                CreateBar
            End If

            'Create a new instance of this class for each textbox
            Set cBar = New clsBar
            cBar.AssignControl Ctl, cmdBar
            'Add it to the control array
            colControls.Add cBar
        End If
    Next Ctl
End Sub

Private Sub Class_Terminate()
    'Delete the commandbar when the class is destroyed
    On Error Resume Next
    cmdBar.Delete
End Sub

'Click event of the copy button
Private Sub cmdCopyButton_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    fmUserform.ActiveControl.Copy
    CancelDefault = True
End Sub

'Click event of the paste button
Private Sub cmdPasteButton_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    fmUserform.ActiveControl.Paste
    CancelDefault = True
End Sub

'Right click event of each textbox
Private Sub tbControl_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If Button = 2 And Shift = 0 Then
        'Display the popup
        cmdBar.ShowPopup
    End If
End Sub

Private Sub CreateBar()
    Set cmdBar = Application.CommandBars.Add(, msoBarPopup, False, True)
    'We’ll use the builtin Copy and Paste controls
    Set cmdCopyButton = cmdBar.Controls.Add(ID:=19)
    Set cmdPasteButton = cmdBar.Controls.Add(ID:=22)
End Sub

'Assigns the Textbox and the CommandBar to this instance of the class
Sub AssignControl(TB As MSForms.TextBox, Bar As CommandBar)
    Set tbControl = TB
    Set cmdBar = Bar
End Sub

Answer 1

获取多页控件上的 ActiveControl 名称

有必要通过辅助函数 (ActiveControlName) 使用 SelectedItem 属性并从那里获取控件(它的名称)来了解多页的选定 Page。按如下方式更改您的按钮点击事件:

类模块clsBar中的相关按钮点击事件

'Click event of the copy button
Private Sub cmdCopyButton_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
Dim sACN As String
sACN = ActiveControlName(fmUserform)    ' find control's name
       ' Debug.Print sACN & ".Copy"
fmUserform.Controls(sACN).Copy          ' << instead of fmUserform.ActiveControl.Copy
CancelDefault = True
End Sub

'Click event of the paste button
Private Sub cmdPasteButton_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
Dim sACN As String
sACN = ActiveControlName(fmUserform)
       ' Debug.Print sACN & ".Paste"
fmUserform.Controls(sACN).Paste    ' << instead of fmUserform.ActiveControl.Paste
CancelDefault = True
End Sub

上面点击事件调用的辅助函数

Function ActiveControlName(form As UserForm) As String
'cf Site: https://stackoverflow.com/questions/47745663/get-activecontrol-inside-multipage
'Purpose: get ActiveControl
 Dim MyMultiPage As MSForms.MultiPage, myPage As MSForms.Page
 If form.ActiveControl Is Nothing Then
    ' do nothing
 ElseIf TypeName(form.ActiveControl) = "MultiPage" Then
    Set MyMultiPage = form.ActiveControl
    Set myPage = MyMultiPage.SelectedItem
    ActiveControlName = myPage.ActiveControl.Name
 Else
    ActiveControlName = form.ActiveControl.Name
 End If
 End Function

旁注

建议在空字符串的情况下检查所选文本字符串的长度，以防止出现不需要的结果。