|
SelectionChangeの度にTimerStop/TimerStartを繰り返すのは非効率のような気がします。
SelectionChangeで変更時間のみを記録して、OnTime実行時にチェック&再セットしてはいかがでしょう。
以下、一例です。
'-------------------------------------------------
'標準モジュール
Option Explicit
Public changeTime As Date
Public setTime As Date
Public Const interval = "0:00:10"
Sub timerStart()
changeTime = Now
setTime = Now + TimeValue(interval)
Application.OnTime setTime, "TimeCheck"
End Sub
Sub TimeCheck()
If setTime - TimeValue(interval) < changeTime Then
setTime = changeTime + TimeValue(interval)
Application.OnTime setTime, "TimeCheck"
Else
Call test
End If
End Sub
Sub test()
UserForm1.Show
End Sub
'-------------------------------------------------
'UserForm1
Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private tm_continue As Boolean
Private Sub CommandButton1_Click()
tm_continue = True
End Sub
Private Sub UserForm_Activate()
Dim i As Integer
Me.Repaint
For i = 10 To 0 Step -1
Me.Label1 = i
DoEvents
If tm_continue Then Exit For
Sleep 1000
Next i
Unload Me
If tm_continue Then
setTime = Now + TimeValue(interval)
Application.OnTime setTime, "TimeCheck"
Else
MsgBox "close" 'Close処理
End If
End Sub
'-------------------------------------------------
'ThisWorkbookモジュール
Option Explicit
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
changeTime = Now
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime setTime, "TimeCheck", , False
End Sub
#常に上書き保存する運用で良いかどうかは別として。
|
|