Help with "Flashing" :)

[golf4]

Hi, everyone (again):

You guys have been so much help with my "novice" questions. I really appreciate all the help.

Still working on my income calculation spreadsheet for work, and I was hoping for some real basic tips on how to set flashing cells. I checked out the Dummies book, but nothing. Are there any books that would helps with basic tips on formatting of this type - something like "if A1 = "X", MAKE B2 FLASH RED"?

Again, thanks for the help.

 

[ylijohe]

Sub FlashBack()
'Joe Was
'Make cell range Background color, flash x times, x fast, in x color,
'when Ctrl-a is pressed.

Dim newColor As Integer
Dim myCell As Range
Dim x As Integer
Dim fSpeed

'Make this cell range background flash!
Set myCell = Range("A1:M8")
Application.DisplayStatusBar = True
Application.StatusBar = "... Select Cell to Stop and Edit or Wait for Flashing to Stop! "

'Make cell background flash to this color!
'Black 25, Magenta 26, Yellow 27, Cyan 28, Violet 29, Dark Red 30,
'Teal 31, Blue 32, White 2, Red 3, Light Blue 41, Dark Blue 11,
'Gray-50% 16, Gray-25% 15, Bright Cyan 8.
newColor = 11

'Make the cell range flash fast: 0.01 to slow: 0.99
fSpeed = 0.2

'Make cell flash, this many times!
Do Until x = 2

'Run loop!
DoEvents
Start = Timer
Delay = Start + fSpeed
Do Until Timer > Delay
DoEvents
myCell.Interior.ColorIndex = newColor
Loop
Start = Timer
Delay = Start + fSpeed
Do Until Timer > Delay
DoEvents
myCell.Interior.ColorIndex = xlNone
Loop
x = x + 1
Loop
Application.StatusBar = False
Application.DisplayStatusBar = Application.DisplayStatusBar
End Sub

Sub FlashFont()
'Joe Was
'Make cell range font flash, x times, x fast, in x color,
'when Ctrl-z is pressed.
Dim newColor As Integer
Dim myCell As Range
Dim x As Integer
Dim fSpeed

'Make this cell range font flash!
Set myCell = Range("A1:AF12")
Application.DisplayStatusBar = True
Application.StatusBar = "... Select Cell to Stop and Edit or Wait for Flashing to Stop! "

'Make cell font flash to this color!
'Black 25, Magenta 26, Yellow 27, Cyan 28, Violet 29, Dark Red 30,
'Teal 31, Blue 32, White 2, Red 3, Light Blue 41, Dark Blue 11,
'Gray-50% 16, Gray-25% 15, Bright Cyan 8.

newColor = 3

'Make the cell range flash fast: 0.01 to slow: 0.99
fSpeed = 0.3

'Make cell flash, this many times!
Do Until x = 2

'Run loop!
DoEvents
Start = Timer
Delay = Start + fSpeed
Do Until Timer > Delay
DoEvents
myCell.Font.ColorIndex = newColor
Loop
Start = Timer
Delay = Start + fSpeed
Do Until Timer > Delay
DoEvents
myCell.Font.ColorIndex = xlAutomatic
Loop
x = x + 1
Loop
Application.StatusBar = False
Application.DisplayStatusBar = Application.DisplayStatusBar
End Sub
Sub reSetFlash()
'Joe Was
'Re-set cell range color if edit break on color, Ctrl-r to re-set!
ActiveCell.Select
Selection.Interior.ColorIndex = xlNone
End Sub

' There´s no such as "dumb question", only dumb question is the one that is not asked.

 

[stevebausch]

If this is code you value, make a copy and archive it.

I don't think you will find it anywhere but here at MrExcel. I tried searching on "flash", "blink" etc. and got nowhere.

 

[Colo]

Hi how about this.(my post)

http://216.92.17.166/board/viewtopic.php?topic=24842&forum=2

 

[Derek]

Hi there
Here's another variation.

Paste code in a module, type your name in A1 (bold font is better), run macro.


Private Declare Sub Sleep Lib "kernel32" (ByVal dwmilliseconds As Long)
Sub Button1_Click()

Dim x As Integer
Dim y As Integer

Do Until Count = 5
x = 1
Do Until x = Len(Range("A1"))
With Range("A1").Characters(Start:=x, Length:=1).Font
.ColorIndex = 3
End With
Sleep 30
Range("A1").Font.ColorIndex = 2
Sleep 30
x = x + 1
Loop
Count = Count + 1
Range("A1").Font.ColorIndex = xlAutomatic
Sleep 40
Loop
Range("A1").Font.ColorIndex = xlAutomatic

End Sub

regards
Derek
This message was edited by Derek on 2002-10-11 04:19

 

[Jack in the UK]

Hi

Nar this code from Joe Was is guru, and can be played with, he started it off and now its all over the place and versions are to.

I have posted in the past and credit Joe with his work, i use a few edited vessions at work myself

NO it started here from Joe Was, Also Ivan, Colo and a few have added guru touches and all are awesome, plain and simple commet from Jack

 

[golf4]

Thanks to everyone for their imput on flashing. I've finally figured out, kind of, how to insert the copied code into a workbook. One more question please: if I wanted the now-created macro to activate when, say, cell A1 = "X", how would I construct the formula - something like =if(A1="X",activate macro XXX(?),"").

Thanks again for the help

 

[Legacy 11273]

Put this in a normal module :-

Dim nextFlash As Date
Sub StartFlash()
nextFlash = Now + TimeValue("00:00:01")
Application.OnTime nextFlash, "StartFlash"
Flash
End Sub

Sub Flash()
With [B1].Font
If [A1] = "X" Then
If .ColorIndex = 3 Then
.ColorIndex = xlAutomatic
Else
.ColorIndex = 3
End If
End If
End With
End Sub

Sub StopFlash()
On Error Resume Next
Application.OnTime nextFlash, "StartFlash", schedule:=False
End Sub


Put this in the ThisWorkbook module :-

Private Sub Workbook_Open()
If [A1].Value = "X" Then Call StartFlash
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopFlash
End Sub


If A1 contains a formula put this in the Sheet module :-

Private Sub Worksheet_Calculate()
If [A1].Value = "X" Then
Call StartFlash
Else
Call StopFlash
[B1].Font.ColorIndex = xlAutomatic
End If
End Sub

Else if data in A1 is via the keyboard, put this in the Sheet module :-

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And [A1] = "X" Then
Call StartFlash
Else
Call StopFlash
[B1].Font.ColorIndex = xlAutomatic
End If
End Sub

 

[s-o-s]

On 2002-10-11 00:42, golf4 wrote:
Hi, everyone (again):

You guys have been so much help with my "novice" questions. I really appreciate all the help.

Still working on my income calculation spreadsheet for work, and I was hoping for some real basic tips on how to set flashing cells. I checked out the Dummies book, but nothing. Are there any books that would helps with basic tips on formatting of this type - something like "if A1 = "X", MAKE B2 FLASH RED"?

Again, thanks for the help.

If you are looking for a different approach, this one flashes a named range of cells until you press Ctrl Shift and S together.

This macro will run in the background and enable you to continue working and flashing the cells whilst allowing input.

The process uses the Application.OnTime process to call the macro.

It uses the cells A1:E1 in the first row for variables so you must not use these in your spreadsheet.

Cell A1 is used to tell when to exit the macro.
Cell B1 is the 1st Colour background to use
Cell C1 is the 2nd Colour background to use
(for the above 2 change the cell background colour to the one required)
Cell D1 set to 1 (Manually set this to 1 to start the process. each time the macro runs it alternates this from 1 to NOT 1.)
Cell E1 set this to "00:00:01" (This the timevalue for the delay.)

Last thing you need to do is to define the range of cells that you want to Flash. This can be accomplished by selecting a single cell, a range or seperate cells whilst holding down the Ctrl key.
Next follow Insert | Name | Define give the range the name "RngToFlash" and then click the Add button.

In my version I then added a button on the First Row and assigned this to the FlashCells macro. Then Group row 1 so that it becomes effectively hidden.

Option Explicit

Sub FlashCells()

Application.ScreenUpdating = False

Dim CurCell As Range
Dim FirstCol, SecndCol, ColToShow As Integer


CurCell = ActiveCell.Address
If [A1] = "Stop" Then Exit Sub

On Error GoTo ErrTrap
    Application.Goto Reference:="RngToFlash"
On Error GoTo 0

Application.OnKey "^+S", "StopFlash"

Application.OnTime Now + [E1], "FlashCells"
    
FirstCol = Range("B1").Interior.ColorIndex
SecndCol = Range("C1").Interior.ColorIndex

If [D1] = 1 Then
    ColToShow = FirstCol
Else
    ColToShow = SecndCol
End If

    Application.Goto Reference:="RngToFlash"

    Selection.Interior.ColorIndex = ColToShow
    
Range(CurCell).Select
[D1] = Not [D1]

GoTo Thend

ErrTrap:

Msg1 = "You have not defined the RngToFlash in Range | Name | Define"
Msg2 = "Define this range then run the macro again"

MsgBox Msg1 & Chr$(10) & Msg2

Thend:
Application.ScreenUpdating = True

End Sub


Sub StopFlash()
Dim CurCell As Range
    
    CurCell = ActiveCell.Address
    Application.Goto Reference:="RngToFlash"
    Selection.Interior.ColorIndex = xlNone
    Range(CurCell).Select
    
    [A1] = "Stop"
    Application.OnKey "^{Esc}", ""
    Application.OnTime Now + [E1] + [E1], "ResetFlash"

End Sub

Sub ResetFlash()

[A1] = ""

End Sub

Repost as something went wrong on the last one.