MrExcel.com - Your One Stop for Excel Tips & Solutions

FLASHER

Posted by ROGER on September 10, 2001 9:41 AM

If a cell (in a range of cell)contains a null value, i want that particular to change colors, somewhat flashing..(to remind the user to enter data)..and the cells dependent on the flashing cell to blink too. for example. A1 cell need input of a number, B1 input of cost, C1=(B1*(1+A1/100), giving the selling price. So incase user forgets A1, that particular cell should flash, reminding the user to enter a value..Thanx

Posted by Nate on September 10, 2001 12:13 PM

This should work:

Dim nextTime As Date

Sub Flash()
nextTime = Now + TimeValue("00:00:01")
If Range("a1,b1").Interior.ColorIndex = 2 Then Range("a1,b1").Interior.ColorIndex = 39 Else Range("a1,b1").Interior.ColorIndex = 2
Application.OnTime nextTime, "Flash"
End Sub

Sub StopIt()
Application.OnTime nextTime, "Flash", schedule:=False
Range("a1,b1").Interior.ColorIndex = xlNone
End Sub

Please note, this is not worksheet defined (you could add this to the range), so it runs on any active worksheet (while running). Also, as I am a Vikings fan, I chose purple, you could change Color Index 39 to one of your choice. Good luck!

Nate

Posted by Nate on September 10, 2001 1:34 PM

Thought this may be more what you're looking for. This will launch the flasher upon opening the file and will kill the flashing once a value is entered into the cell:

Dim nextTime As Date
Sub auto_open()
Application.Run ("FlashA")
Application.Run ("FlashB")
End Sub

Sub FlashA()
nextTime = Now + TimeValue("00:00:01")
If Range("a1").Interior.ColorIndex = xlNone Then Range("a1").Interior.ColorIndex = 39 Else Range("a1").Interior.ColorIndex = xlNone
If Range("a1") <> "" Then
Application.OnTime nextTime, "FlashA", False
Application.OnTime nextTime, "FlashA", schedule:=False
Range("a1").Interior.ColorIndex = xlNone
Else: Application.OnTime nextTime, "FlashA"
End If
End Sub

Sub FlashB()
nextTime = Now + TimeValue("00:00:01")
If Range("b1").Interior.ColorIndex = xlNone Then Range("b1").Interior.ColorIndex = 39 Else Range("b1").Interior.ColorIndex = xlNone
If Range("b1") <> "" Then
Application.OnTime nextTime, "FlashB", False
Application.OnTime nextTime, "FlashB", schedule:=False
Range("b1").Interior.ColorIndex = xlNone
Else: Application.OnTime nextTime, "FlashB"
End If
End Sub

Sub StopIt()
Application.OnTime nextTime, "FlashA", schedule:=False
Application.OnTime nextTime, "FlashB", schedule:=False
Range("a1,b1").Interior.ColorIndex = xlNone
End Sub


Again, no worksheets defined. Also, you may want to set up event handlers if you'd like the flashing to resume up the deletion of the cell entries.....

Happy Hunting!

Nate

Posted by Nate on September 10, 2001 2:25 PM

All right, I'll probably stop responding to myself one of these posts, but I handled my nextTime definition very sloppily in my last post, hate to leave you with that. Here's an update for the class module:

Dim nextTime1 As Date
Dim nextTime2 As Date
Sub auto_open()
Application.Run ("FlashA")
Application.Run ("FlashB")
End Sub

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

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

Sub StopIt()
Application.OnTime nextTime1, "FlashA", schedule:=False
Application.OnTime nextTime2, "FlashB", schedule:=False
range("a1,b1").Interior.ColorIndex = xlNone
End Sub
Sub auto_close()
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

And an Event Handler for the Sheet1.module:

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


Two changes other than my definitions. The first worksheet is defined. Second, if the entry in a1 or b1 is deleted, the cell will flash again. Sorry you asked yet? Best of luck.....


Nate