vba 如何通过单击命令按钮停止宏？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28188841/
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
How to stop a macro by clicking a command button?
提问by Eivind Sageng
I have a code to copy a range and paste the values.
我有一个代码来复制一个范围并粘贴这些值。
This is done in a time interval after I click a command button.
这是在我单击命令按钮后的时间间隔内完成的。
How do I stop or pause this macro by clicking a button?
如何通过单击按钮停止或暂停此宏？
Here is the main code:
下面是主要代码：
Sub timestamp()
'
' timestamp Macro
'
N = WorksheetFunction.Count(Sheets("DNB").Columns(1))
dnbspread = Sheets("DNB").Range("G5:G30")
Sheets("DNB").Cells(N + 34, 1) = Date
Sheets("DNB").Cells(N + 34, 2) = Time
Sheets("DNB").Range("G5:G30").Copy
Sheets("DNB").Cells(N + 34, 3).PasteSpecial Transpose:=True, Paste:=xlPasteValues
Application.OnTime Now + TimeValue("00:00:05"), "timestamp"
End Sub
I tried a couple of things.
我尝试了几件事。
- by BREAK function
- 通过 BREAK 功能
Sub PauseMacro()
Application.SendKeys "^{BREAK}"
End Sub
2.
2.
Public StopMacro as Boolean
Sub SetStopMacro()
StopMacro = True
End Sub
and put it in the code as this:
并将其放入代码中，如下所示：
Sub timestamp()
'
' timestamp Macro
'
N = WorksheetFunction.Count(Sheets("DNB").Columns(1))
dnbspread = Sheets("DNB").Range("G5:G30")
Sheets("DNB").Cells(N + 34, 1) = Date
Sheets("DNB").Cells(N + 34, 2) = Time
Sheets("DNB").Range("G5:G30").Copy
Sheets("DNB").Cells(N + 34, 3).PasteSpecial Transpose:=True, Paste:=xlPasteValues
Application.OnTime Now + TimeValue("00:00:10"), "timestamp"
DoEvents
If StopMacro = True Then Exit Sub
End Sub
回答by dee
Public StopMacro As Boolean
Sub SetStopMacro()
StopMacro = True
End Sub
Sub timestamp()
'
' timestamp Macro
'
' code here
'
' Put If-Then before Application.OnTime call.
' So prevent next call of 'timestamp()' if StopMacro equals to true
If Not StopMacro Then
Application.OnTime Now + TimeValue("00:00:15"), "timestamp"
End If
End Sub
回答by Alejandro Gizzi
You can declare a global variable inside the module where the macro is running from, and then, when the command button is clicked, assign a value to that variable you just declared. Then, create an if statement, where the code exits if the variable equals to that value.
您可以在运行宏的模块内声明一个全局变量，然后，当单击命令按钮时，为您刚刚声明的该变量赋值。然后，创建一个 if 语句，如果变量等于该值，则代码退出。
Private Sub CommandButton2_Click()
btncancel = 1
EMSetup.hide
Exit Sub
End Sub
For the command button.
对于命令按钮。
Global btncancel As Integer
If btncancel = 1 Then
Exit Sub
End If
Hope this helps!
希望这可以帮助！
