插入了在新行中运行宏的 Excel VBA

Excel VBA to run Macro in new row is Inserted

提问by BannerM

I am trying to have my spreadsheet automatically take the previous rows format and formulas when a new row is inserted.

我试图让我的电子表格在插入新行时自动采用以前的行格式和公式。

I read where you can set up your sheet to automatically run the code if a change is made, but I am having a hard time getting the code to work.

我读过您可以在哪里设置工作表以在进行更改时自动运行代码，但是我很难让代码正常工作。

I have tried the following and every time I insert a new row it keeps adding a row until it gets an error and I have to force quit:

我尝试了以下操作，每次插入新行时，它都会添加一行，直到出现错误，我必须强制退出：

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Range("A1:D25") = ActiveCell.EntireRow.Insert Then
        Cells(1, 2).Value = 10
    End If 
End Sub

I added the Cell Value = 10to see if it would work. It was just a test, but it still fails.

我添加了Cell Value = 10看看它是否有效。这只是一个测试，但它仍然失败。

Does anyone know a possible solution?

有谁知道可能的解决方案？

回答by chris neilsen

There are two main issues in your code

您的代码中有两个主要问题

1. You are causing an Event Cascade. Ie your Change event is triggering further change events
2. .Insertdoesn't do what you seem to think it does. It doesn't detectinserted rows, it Insertsrows.

1. 您正在导致事件级联。即您的 Change 事件正在触发进一步的 change 事件
2. .Insert不会做你认为它会做的事情。它不检测插入的行，它插入行。

I am assuming by "... insert a new row ..."you mean Insert a whole row

我假设“......插入一个新行......”你的意思是插入一整行

This demo avoids the cascade with .EnableEvents = Falseand uses Copy, pasteSpecialto copy formats and formulas.

这个演示避免了级联.EnableEvents = False和使用Copy,pasteSpecial来复制格式和公式。

Option Explicit

Dim RowsCount As Long ' Variable to track number of rows used in sheet

Private Sub Worksheet_Activate()
    RowsCount = Me.UsedRange.Rows.Count
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo EH
    ' Detect whole row changed
    If Target.Columns.Count = Me.Columns.Count Then
        ' Detect Extra Row
        If RowsCount = Me.UsedRange.Rows.Count - 1 Then
            ' Copy Formulas and Format new row
            Application.EnableEvents = False
            If Target.Row > 1 Then
                Target.Offset(-1, 0).Copy
                Target.PasteSpecial xlPasteFormulas, xlPasteSpecialOperationNone, False, False
                Target.PasteSpecial xlPasteFormats, xlPasteSpecialOperationNone, False, False
                Application.CutCopyMode = False
            End If
        End If
        RowsCount = Me.UsedRange.Rows.Count
    End If

EH:
    Application.EnableEvents = True
End Sub

回答by laughsloudly

It looks like you are attached to the wrong event. You have attached your code to the "Worksheet_Change" event, but your code is also causing a change to the worksheet

看起来您加入了错误的事件。您已将代码附加到“Worksheet_Change”事件，但您的代码也导致工作表发生更改

Cells(1, 2).Value = 10

Which turns right around and invokes the "Worksheet_Change" event.

右转并调用“Worksheet_Change”事件。

As for the right event to attach to, it looks like there is no native event for "New Row Inserted".

至于要附加到的正确事件，似乎没有“插入新行”的本机事件。

There is a discussion of this over on this pagewhich might be an answer to your problem...

在此页面上对此进行了讨论，这可能是您问题的答案...

回答by n8.

Like @laughsloudly said, what your code is doing now is once you make a change anywhere in the range A1:D25 it will start inserting rows until Excel runs out of rows, it's an open loop.

就像@laughsloudly 所说的那样，您的代码现在所做的是，一旦您在 A1:D25 范围内的任何位置进行更改，它就会开始插入行，直到 Excel 用完行，这是一个开环。

The code:

编码：

If Target.Range("A1:D25") = ActiveCell.EntireRow.Insert Then

Is meant to check whether the action taken is within the range you want to be monitored. You don't want to be performing an action in this line. Rather, you want something more like:

用于检查所采取的操作是否在您要监视的范围内。您不想在此行中执行操作。相反，你想要更像：

If Target.Range("A1:D25") = ActiveCell Then

This will allow you to run code based on actions in your sheet. However, your statement "I am trying to have my spreadsheet automatically take the previous rows format and formulas when a new row is inserted." isn't entirely logical. I presume that you mean to copy all formatsfrom the above row and only formulas from certain cells, correct? So, let's say if you have rows that all have formulas relative to column A, you don't want to copy all of the row, because you will overwrite A. Furthermore, in this case, you only want to monitor column A.

这将允许您根据工作表中的操作运行代码。但是，您的声明“我试图让我的电子表格在插入新行时自动采用以前的行格式和公式。” 不完全合乎逻辑。我想您的意思是复制上一行中的所有格式，并且只复制某些单元格中的公式，对吗？因此，假设您有所有行都具有相对于 A 列的公式，您不想复制所有行，因为您将覆盖 A。此外，在这种情况下，您只想监视 A 列。

So let's say you have formulas in columns B through K that rely on column A, then you only want changes to column A to affect the spreadsheet. Then your code would look something like this:

因此，假设您在 B 到 K 列中有依赖于 A 列的公式，那么您只希望对 A 列的更改影响电子表格。然后你的代码看起来像这样：

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = ActiveCell.Column Then
        refRow = Target.Row - 1
        thisRow = Target.Row
        Range("B" & refRow & ":K" & refRow).Copy Range("B" & thisRow & ":K" & thisRow)
    End If

End Sub

This copies down everything but column A when you make a change to A. And, as alluded to previously, you don't want the code making any changes to column A (in this case) without something to break it out of that recursive loop. Best just to apply conditional formatting to any column that you are assigning as the target.

当您对 A 进行更改时，这会复制除 A 列之外的所有内容。而且，如前所述，您不希望代码对 A 列（在本例中）进行任何更改而没有将其从递归循环中解脱出来. 最好只是将条件格式应用于您指定为目标的任何列。

Hope that helps,

希望有所帮助，

n8

n8