将双击事件动态添加到新工作表。VBA

Question

VBA。我已经知道如何在改变的单元格上添加触发事件，但由于某些原因，我无法对单击的单元格执行此操作。最终，我希望当用户双击A栏时触发事件。这是我能得到的最大结果。

Sub CreateEventProcedure() 
    Dim VBProj As VBIDE.VBProject 
    Dim VBComp As VBIDE.VBComponent 
    Dim CodeMod As VBIDE.CodeModule 
    Dim LineNum As Long 
    Const DQUOTE = """" ' one " character  

    Set VBProj = ActiveWorkbook.VBProject 
    Set VBComp = VBProj.VBComponents("ThisWorkbook") 
    Set CodeMod = VBComp.CodeModule 
    
    With CodeMod 
        LineNum = .CreateEventProc("Open", "Workbook") 
        LineNum = LineNum + 1 
        .InsertLines LineNum, "    MsgBox " & DQUOTE & "Hello World" & DQUOTE 
    End With 
End Sub 

Answer 1

请尝试下一个代码：

Sub CreateEventDBlClickProcedure()
    Dim VBProj As VBIDE.VBProject, VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule, LineNum As Long

    Set VBProj = ActiveWorkbook.VBProject
    Set VBComp = VBProj.VBComponents("ThisWorkbook")
    Set CodeMod = VBComp.CodeModule
    'Check if the event is not already created:
    If CodeMod.Find("Private Sub Workbook_SheetBeforeDoubleClick", 1, 1, CodeMod.CountOfLines, 1, False) = True Then
            MsgBox "Event already created...": Exit Sub
    End If

    With CodeMod
        .AddFromString "Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)" & vbCrLf & _
                       "    MsgBox  ""Hello World from sheet "" & Sh.Name" & vbCrLf & _
                       "End Sub"
    End With
End Sub