Excel VBA-获取更改工作表事件代码以适用于新复制的范围 [英] Excel vba- Get code of change worksheet event to work for newly copied range

问题描述

问题真的很简单.我在工作表的change事件中有一个带相交命令的代码，在工作表上还有一个按钮，单击该按钮可以将一系列单元格(将change事件中的代码写入其中)复制到单元格中的新位置同一张纸.

Question is simple really. I have some code in the change event of a worksheet with an intersect command.I also have a button on the sheet which when clicked copies a range of cells (for which the code in the change event is written) to a new location in the same sheet.

问题是如何使change事件的代码适用于刚粘贴按钮的宏的新范围.

Question is how do I get the code of the change event to work for the new range I just pasted with the macro of the button.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C6:C30")) Is Nothing Then
        UnprotectSheet ActiveSheet

        If Not Target.Value = "" Then
            Target.Offset(0, -1).Value = Now()
        Else
            Target.Offset(0, -1).ClearContents
        End If

        ProtectSheet ActiveSheet
    End If
End Sub

按钮代码:

Sub Macro()

Range("B3:F3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("N3").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
End Sub

推荐答案

我建议始终将按钮中新复制的范围添加到命名范围(此示例中为"MyName" )，然后使用 Worksheet_Change 中的命名范围与以下项相交:

I suggest to always add the new copied range in your button to a named range ("MyName" in this example) and then use that named range in the Worksheet_Change to intersect with:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("MyName")) Is Nothing Then

---

在按钮宏中使用下面的 Test 过程中的类似操作，将新复制的范围添加到命名范围.

---

Use something like in the Test procedure below in your button macro to add the new copied range to the named range.

Public Sub Test()
    AddRangeToNamedRange "MyName", Selection 'instead of selection better specify a range
                                             'like Range("A1:C5")
    Debug.Print Range("MyName").Address 'shows which addresses are represented by the named range MyName
End Sub


'**
'* Adds a new range to an existing named range.
'* If the named range does not exist yet it adds a named range with the
'* given range name and the given range.
'*
Public Sub AddRangeToNamedRange(RangeName As String, AddNewRange As Range)
    Dim NamedRange As Range

    On Error GoTo NameDoesNotExists
    Set NamedRange = Range(RangeName) 'throws error if name does not exists
    On Error GoTo 0

    'add new range to the named range
    ThisWorkbook.Names(RangeName).RefersTo = Union(NamedRange, AddNewRange)

    Exit Sub
NameDoesNotExists:
    Err.Clear
    ThisWorkbook.Names.Add RangeName, AddNewRange 'add the name (first time)
End Sub

通过这种方式， Range("MyName")始终代表使用按钮复制的所有复制范围.

This way the Range("MyName") always represents all copied ranges that were copied with your button.

这篇关于Excel VBA-获取更改工作表事件代码以适用于新复制的范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！