避免在另一个Excel文件中运行宏 [英] Avoid macro running in another Excel file

问题描述

此代码与.xlsm文件关联，比方说X.当我打开X文件然后打开第二个文件Y时，在Y中按下删除"或退格"键时，X中的宏也会运行到Y中.此外，如果我打开X和Y然后关闭X，但Y仍处于打开状态-如果在Y中按删除"或退格"键，则会自动打开X文件.因此，我想避免这种情况，我希望X中的代码仅在X内部运行.希望不要太困惑！

This code is associated with a .xlsm file, lets say, X. When I open X file and then open a second file Y, the macro from X runs into Y as well when "delete" or "backspace" is pressed in Y. Moreover, if I open X and Y and then close X, but Y is still opened - if I press "delete" or "backspace" in Y, X file will be automatically opened. So, I want to avoid that, I want that the code in X just run inside X. Hope it isn't too confused!

Private Sub Worksheet_Change(ByVal Target As Range)

Dim TestCell
Dim RE As Object
Dim REMatches As Object
Dim Cell1_1 As String
Dim Today As String
Dim Cell As String

ThisRow = Target.Row

With Worksheets("Input")
Application.OnKey "{DELETE}", "CleanCell1_1"

Application.OnKey "{BACKSPACE}", "CleanCell1_1"
End With

If Target.Column = 9 Then

Application.ScreenUpdating = False

ActiveSheet.Unprotect
Columns("I:I").Columns.AutoFit
Sheets("Input").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True
    Sheets("Input").EnableSelection = xlUnlockedCells

Sheets("Chart").Unprotect
Sheets("Chart").Columns("B:B").Columns.AutoFit
Sheets("Chart").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("Chart").EnableSelection = xlNoRestrictions

Application.ScreenUpdating = True

End If

If Target.Column = 10 Then

Set RE = CreateObject("vbscript.regexp")

With RE
    .MultiLine = False
    .Global = False
    .IgnoreCase = True
    .Pattern = "[G,g,Y,y,R,r]"
End With

For Each TestCell In Target.Cells

Set REMatches = RE.Execute(TestCell.Value)

If REMatches.Count > 0 And Len(Target.Value) = 1 Then
    If Len(Cells(1, 1).Value) = 1 Then
        Today = Now()
        Cell1_1 = Sheets("Input").Cells(1, 1).Value
        Range("K" & ThisRow) = Cell1_1 + ": " + Format(Today, "ddmmmyy")
    End If

ElseIf Target.Value <> vbNullString Then
     Row = Target.Row
     Cells(Row, 10).Value = vbNullString
     MsgBox "Please, type only:" & vbNewLine & vbNewLine & "G for Green" & vbNewLine & "Y for Yellow" & vbNewLine & "R for Red"

End If

Next

End If

End Sub

推荐答案

当X工作簿处于活动状态/不活动状态时，我将激活/停用Application.OnKey
像这样:
在本工作簿中:

I would activate/deactivate the Application.OnKey when the X workbook is active/not active
Like this:
In ThisWorkbook:

Private Sub Workbook_Activate() 
    Application.OnKey "{DELETE}", "CleanCell1_1"
    Application.OnKey "{BACKSPACE}", "CleanCell1_1"
End Sub

Private Sub Workbook_Deactivate() 
    Application.OnKey "{DELETE}", ""
    Application.OnKey "{BACKSPACE}", ""
End Sub

这篇关于避免在另一个Excel文件中运行宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！