VBA：如何在没有按钮的情况下自动触发宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45652236/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA: How to automatically trigger macro without a button
提问by Kinghin245
I have made a macro that auto fill the formula on sheet1 whenever the row number of sheet2 is changed.
我制作了一个宏，每当 sheet2 的行号发生变化时，它就会自动填充 sheet1 上的公式。
Is it possible to trigger it automatically without a button when i have any update on sheet2?
当我在 sheet2 上有任何更新时，是否可以在没有按钮的情况下自动触发它？
Sub Autofill()
Dim sg As Sheets
Dim Row As Long
Dim fillRow As Integer
Application.EnableEvents = False
Row = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
fillRow = Row - 1
Sheets("Sheet1").Select
Range("A1:E1").Select
Selection.Autofill Destination:=Range("A1:E" & fillRow), Type:=xlFillDefault
Application.EnableEvents = True
End Sub
回答by Marco
You could try to create a sub like following:
您可以尝试创建一个如下所示的子项：
Paste the following code. And change: 1) "D4" with your cells you want to "monitor" 2) Paste your macro in the line "Do things"
粘贴以下代码。并更改：1）“D4”与您要“监视”的单元格 2）将您的宏粘贴到“做事”行中
The problem is, your code is run everytime the focus is changed to another cell. But you could also use Worksheet_BeforeDoubleclick if this is enough. Then every time you clicke twice the code will run
问题是，每次将焦点更改为另一个单元格时，您的代码都会运行。但是，如果这足够了，您也可以使用 Worksheet_BeforeDoubleclick。然后每次点击两次代码都会运行
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
If Not Intersect(Target, Range("D4")) Is Nothing Then
'Do things
End If
End If
End Sub
结束子
回答by Mahmoud Mostafa
Right-click on the sheet tab at the bottom of the scree, click 'view code' then insert this following code in there.
右键单击屏幕底部的工作表选项卡，单击“查看代码”，然后在其中插入以下代码。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Row As Long
Dim fillRow As Integer
This next line will exit the code if column A is not what is being changed on the sheet. Delete it if you want the code to be triggered by any change on any cell of the sheet.
如果 A 列不是工作表上正在更改的内容，则下一行将退出代码。如果您希望代码由工作表的任何单元格上的任何更改触发，请删除它。
if InRange(Target,Worksheets("Sheet2").range("A:A") = false then exit sub
Row = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
fillRow = Row - 1
Sheets("Sheet1").Select
Range("A1:E1").Select
Selection.Autofill Destination:=Range("A1:E" & fillRow), Type:=xlFillDefault
End Sub
Function InRange(Range1 As Range, Range2 As Range) As Boolean
InRange = Not (Application.Intersect(Range1, Range2) Is Nothing)
End Function
回答by McWilliamsPete
use a Worksheet.SelectionChange-Event.
使用 Worksheet.SelectionChange-Event。
in the Worksheet-VBA for sheet2 add:
在 sheet2 的 Worksheet-VBA 中添加：
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Call Autofill() End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Call Autofill() End Sub
(This will be triggered if cell is changed, even if user does not leave the row, so check parameter Target.)
（如果单元格更改，即使用户没有离开该行，也会触发此操作，因此请检查参数 Target。）
