View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.misc
chris chris is offline
external usenet poster
 
Posts: 17
Default close workbook without closing excel and stop recursive function

I earlier implemented a feature in one macro to allow blinking text.
The code is as follows:

Sub StartBlink()
Range("A1").Font.ColorIndex = RandomNumber(2, 56)
'Pauses it by a second
RunWhen = Now + TimeSerial(0, 0, 1)

'Restarts the function
Application.OnTime RunWhen, "StartBlink", , True
End Sub

Sub StopBlink()
Range("A1").Font.ColorIndex = xlColorIndexAutomatic

'Breaks the StartBlink function from continuing to run
Application.OnTime RunWhen, "StartBlink", , False
End Sub

I have the following code that calls the functions upon workbook open
and close in the This Workbook sheet:

Private Sub Workbook_Open()
'Opens the StartBlink function when the file opens
StartBlink
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Stops the Stopblink function to prevent error message upon exit

StopBlink

End Sub

This seemed to work great until I added a button to save and close and
prompt users if they wanted to exit excel entirely or just close the
workbook:

Private Sub CommandButton2_Click()
'Changes Caption back to "Stop Blink" for next time
CommandButton1.Caption = "Stop Blink"

'Saves Workbook
ActiveWorkbook.Save

'Prompts if you want to close Excel entirely or just close the
current window you are in
If MsgBox("Do you want to exit Excel?", vbYesNo, "Exit Microsoft
Excel") = vbYes Then
Application.Quit
Else
Workbooks("Wedding List.xls").Close
End If
End Sub

If they choose to not exit excel, it appears to close the workbook.
However, it just reopens the workbook a second later when it apparently
calls the StartBlink function again. Anyway to modify some of this
code to allow users to close the workbook for good without closing any
other excel windows? Thanks in advance!