MrExcel Publishing
Your One Stop for Excel Tips & Solutions

need function ' BLINK() '


Posted by kenan bayar on January 09, 2002 2:06 PM

need user defined function BLINK(),to be used in a formula so conditionaly a cell would be blink/flash.


Posted by Qroozn on January 09, 2002 4:25 PM

This is some code i found written by chip pearson.
see if you can adopt it to your needs... or if someone can add onto it.

Sub flash()
Dim runwhen As Double
runwhen = Now + TimeSerial(0, 0, 1)
Application.OnTime runwhen, "flashtext"
End Sub
Sub flashtext()
With Range("a1").Font
If .ColorIndex = xlColorIndexAutomatic Then
.ColorIndex = 3
Else
.ColorIndex = xlcolorindexautomatix
End If
End With
runwhen = Now + TimeSerial(0, 0, 1)
Application.OnTime runwhen, "flashtext"
End Sub
Sub stopflash()
Application.OnTime runwhen, "flashtext" = False
End Sub

End Sub

Posted by Nate Oliver on January 09, 2002 4:33 PM

One Way

I'm not sure I recommend using this (I think you'd be better off with a solid cell) but here's what I got. It's set up to flash a1 & b1 independantly of each other on the 1st worksheet. You should be able to alter the sheet and or ranges as necessary.

Two Steps:

Step1:

Right-click on your worksheet and select 'View Code.' This will take you to the appropriate 'Class module.' Paste the following code in:

Private Sub Worksheet_Change(ByVal target As range)
If a1 = "" Then Application.Run ("FlashA")
If b1 = "" Then Application.Run ("FlashB")
End Sub

Step 2:

Insert a normal macro module and paste in the following code:

Dim nextTime1 As Date
Dim nextTime2 As Date

Private Sub auto_open()
Application.Run ("FlashA")
Application.Run ("FlashB")
End Sub

Private Sub FlashA()
nextTime1 = Now + TimeValue("00:00:01")
If Worksheets(1).range("a1").Interior.ColorIndex = xlNone Then Worksheets(1).range("a1").Interior.ColorIndex = 39 Else Worksheets(1).range("a1").Interior.ColorIndex = xlNone
If Worksheets(1).range("a1") = "" Then
Application.OnTime nextTime1, "FlashA"
Else: Application.OnTime nextTime1, "FlashA", False
Application.OnTime nextTime1, "FlashA", schedule:=False
range("a1").Interior.ColorIndex = xlNone
End If
End Sub

Private Sub FlashB()
nextTime2 = Now + TimeValue("00:00:01")
If Worksheets(1).range("b1").Interior.ColorIndex = xlNone Then Worksheets(1).range("b1").Interior.ColorIndex = 39 Else Worksheets(1).range("b1").Interior.ColorIndex = xlNone
If Worksheets(1).range("b1") = "" Then
Application.OnTime nextTime2, "FlashB"
Else: Application.OnTime nextTime2, "FlashB", False
Application.OnTime nextTime2, "FlashB", schedule:=False
range("b1").Interior.ColorIndex = xlNone
End If
End Sub

Private Sub StopIt()
Application.OnTime nextTime1, "FlashA", schedule:=False
Application.OnTime nextTime2, "FlashB", schedule:=False
range("a1,b1").Interior.ColorIndex = xlNone
End Sub

Private Sub auto_close()
Application.Run ("auto_open")
If Worksheets(1).range("a1") = "" Then Application.OnTime nextTime1, "FlashA", schedule:=False
range("a1").Interior.ColorIndex = xlNone
If Worksheets(1).range("b1") = "" Then Application.OnTime nextTime2, "FlashB", schedule:=False
range("b1").Interior.ColorIndex = xlNone
End Sub