Stop Macro Befor close

[G2K]

Hi all,

I am using a timer to blink a range on worksheet

Code -

Const Interval As Double = 1 / 24 / 60 / 60 / 1 '1/2seconds
Const MaxNum As Integer = 100
Dim Num As Integer

Public Sub Blink()
Dim r, rg As Range
Set r = Sheets(1).Range("F3:G8")
Set rg = Sheets(1).Range("D3:E8")

If r.Interior.ColorIndex = 2 Then
r.Interior.ColorIndex = 3
rg.Font.ColorIndex = 2
ElseIf r.Interior.ColorIndex <> 2 Then
rg.Font.ColorIndex = 6
r.Interior.ColorIndex = 2

End If
Num = Num + 1

'start next blink
If Num < MaxNum Then
Application.OnTime Now + Interval, "Blink"

Else
Num = 1
End If

End Sub


Public Sub StopBlink()
On Error Resume Next
Application.OnTime Now, "Blink", Schedule:=False
Sheets(1).Range("D3:E8").Font.ColorIndex = 2
End Sub

when i try to close the workbook before timer finishes it's itereation, i get a new excel window with macro security warning and it opens same workbook again and i get same macro runing.and it happend again and again until macro finishes it's iteration. i have tried to close this by using before close event but unfortunately it's not working. i just want to stop blinking and close workbook without saving it

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call StopBlink
ActiveWorkbook.Close False
End Sub

Please help.........

 

[BrianB]

Are you saying the workbook does close ?

If so, you might try

ActiveWorkbook.Close False
End

 

[G2K]

Yes, Workbook Does Close, but it repoens automaticaly and i found the macro in running state and unfortunately follwoing code is not working or not able to stop the macro before close -

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call StopBlink
ActiveWorkbook.Close False
End
End Sub

any idea????

 

[BrianB]

You might try setting a variable to check for file closure
eg.

'- beginning of code
Dim FileClose As Boolean
FileClose = False

'- blink routine
If Num < MaxNum And FileClose = False Then
Application.OnTime Now + Interval, "Blink"

'- workbook close
FileClose = True
ActiveWorkbook.Close False