FLASHING FONT CODE VERY BUGGY, PLEASE FIX

[Noir]

The code below works fairly well but, the problem is once the code starts running it's almost impossible to close your workbook. The only way i have found to close is to click "Pause" from within the VB edit window, then close out of the workbook.

Please look at this code and see if you can correct any problems.

Here is the code;

Placed in a new Module:

Sub Flash()
NextTime = Now + TimeValue("00:00:01")
With Cells(9, 8).Font
If .ColorIndex = 2 Then .ColorIndex = 3 Else .ColorIndex = 2
End With
Application.OnTime NextTime, "Flash"
End Sub

Sub StopIt()
Application.OnTime NextTime, "Flash"
Application.OnTime NextTime, "Flash", Schedule:=False
Cells(9, 8).Font.ColorIndex = xlAutomatic
End Sub




Placed in This Workbook:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Calculate
If Cells(9, 8).Value > 0 Then
Flash
Else
StopIt
End If

End Sub


Thx,
Noir

 

[TheClayTaurus]

what does this do??

 

[Noir]

Causes the font in the designated cell to flash or blink "Red" constantly. You can continue to work on your sheet as usual and the code will not stop flashing.

 

[TheClayTaurus]

So you can work on the worksheet without any problems, but when you try to close the workbook you run into problems... i'm guessing it's because once your blinking code starts it's constantly running code... the whole thing seems like there would be a better way then having a looping VB code, but that's just me I guess. Any reason there has to be blinking text, or is it just something to look spiffy?

 

[Iridium]

From http://j-walk.com/ss/excel/eee/eee002.txt

Created by Bill Manville

To create a blinking cell:

If you define a new Style (Format / Style / Flash/ Add ) and apply
that style to the cells you want to flash, paste the following code
into a module sheet and run the procedure Flash from Auto-Open if
desired you will get the text flashing alternately white and red.

Dim NextTime As Date

Sub Flash()
NextTime = Now + TimeValue("00:00:01")
With ActiveWorkbook.Styles("Flash").Font
If .ColorIndex = 2 Then .ColorIndex = 3 Else .ColorIndex = 2
End With
Application.OnTime NextTime, "Flash"
End Sub

Sub StopIt()
Application.OnTime NextTime, "Flash", schedule:=False
ActiveWorkbook.Styles("Flash").Font.ColorIndex = xlAutomatic
End Sub

 

[Noir]

Thanks for the code Chris but, i could not get it to work at all.

Noir

 

[Michele]

http://www.mrexcel.com/board2/viewtopic.php?t=34888&highlight=flash+blink+cell

 

[Iridium]

http://www.mrexcel.com/board2/viewtopic.php?t=34888&highlight=flash+blink+cell

Aaah - the Lords of Rock - Smashing Pumpkins...

(sorry for the unrelated post!)

 

[NateO]

Just don't use the style, unless you follow Bill Manville's process. His adjusted code:

<font face=Courier New><SPAN style="color:darkblue">Dim</SPAN> NextTime <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Date</SPAN>

<SPAN style="color:darkblue">Sub</SPAN> Flash()
  NextTime = Now + TimeValue("00:00:01")
  <SPAN style="color:darkblue">With</SPAN> [h9].Font
    <SPAN style="color:darkblue">If</SPAN> .ColorIndex = 2 <SPAN style="color:darkblue">Then</SPAN> .ColorIndex = 3 <SPAN style="color:darkblue">Else</SPAN> .ColorIndex = 2
  <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
  Application.OnTime NextTime, "Flash"
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN>

<SPAN style="color:darkblue">Sub</SPAN> StopIt()
  Application.OnTime NextTime, "Flash", schedule:=<SPAN style="color:darkblue">False</SPAN>
  [h9].Font.ColorIndex = xlAutomatic
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

Is it just me or is:

Duly noted J. Thanks for the heads up to not bother in the future.

not really funny? Laughed out loud after that one...

 

[tusharm]

For a different, not necessarily better, approach see http://www.google.com/groups?selm=MPG.1964b9c85563f22698ab82@msnews.microsoft.com