Flashing messages

[flightjock]

Is it possible to produce a flashing message when the contents of a cell have a specific value?

[Jack in the UK]

not so sure about flashing message (you need a VBA guru to solve that well beyond me sadly)

but don’t forget that format validation will allow pop up message to said conditions, or you can cell validate so the cell say changes colour

Probably not really what you want, but just in case you have though flashing message and don’t know about the other options.

Hope some help towards you problem

[Noir]

Hey Flightjock,
A guy named Dan Aragonist wrote some "Flashing" code for me a while ago. In my case, i wanted my text to flash at all times. Here is the code;

**Place this code in "This Workbook";

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

End Sub

**Place this code in a "Module";

Sub Flash()
NextTime = Now + TimeValue("00:00:01")
With Cells(9, .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, .Font.ColorIndex = xlAutomatic
End Sub

**Edit code to suite your needs.

Good Luck,
Noir

[flightjock]

OK, Thanks so far. However I need some more guidance. I would like to flash a message in red in cell N1 when cell M3 is empty. If M3 is filled the message should disappear. Can you help me a bit more specifically?

[SubZero]

Put this in N1
=IF(M3>="","WARNING","")

Substitute Warning for whatever you want it to say! Just set the cell format on N1 to display Red Text.

I can't do the flash but this is how I do it on late invoices etc. Good Luck, I will keep looking as the flash idea would be a great feature! Mix this with the code above and you should be OK?

[Dexter]

FlightJock,

Here is some code I acquired awhile back that makes a cell flash. I have not worked with it so cannot answer questions on it.
But it works. Good luck!

Dexter

Sub FlashBack()
'Make cell range Background color, flash x times, x fast, in x color,
'when Ctrl-a is pressed, you must set this macro option.

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

'Make this cell range background flash!
Set myCell = Range("A1:A2")
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 = 27

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

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

'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()
'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:A2")
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 = 20

'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()
'Re-set cell range color if edit break on color, Ctrl-r to re-set!
ActiveCell.Select
Selection.Interior.ColorIndex = xlNone
End Sub

[Jack in the UK]

*********** the below code i wish to thank and credit to a old pal over some time Joe Was now MVP from USA Florida*************

maybe we can get some extra posts of his fine work ! he he


This you might like on the note of flashing _

I did not write this, and sorry to the guy that did, ive lost them details as ive palyed arround with this many times, even having randon fonyts flash randon colours

im a a sick man i know | I chose red!


Sub Flashing_Cell_Fonts()

Set myCell = Range("A1:A2")
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 = 20

'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()
'Re-set cell range color if edit break on color, Ctrl-r to re-set!
ActiveCell.Select
Selection.Interior.ColorIndex = xlNone
End Sub


_________________
If you can help a guy in trouble -
If you can sort that nagging problem -
Pease try, at home, at work or on a message board.

Others help you!
So PLEASE help if you can - If only the once.

Thank you -

Rdgs
======

[ This Message was edited by: Jack in the UK on 2002-05-16 06:57 ]

[Joe Was]

Jack, Dexter;

You guy's are fast you beat me to post with my own code!

When I wrote the code you posted it was to try and solve the same question as above on the old board. I was never happy with the above Flash code nor have I been able to find a better way of doing it, yet. It would be nice if MS just added this property to the basic Excel set. JSW

[Jack in the UK]

Hi Joe its Jack

you have seen my comments about you a few times i bet, cant say you post enough for my liking, but as i say some good code and solutions

MVP... not before time my friend well done

ill edit my post and correctly credit you as you rightly deserve, and ill edit my back up to reflect this, i have edited it many times and made it all rather silly.

dam good stuff

[Joe Was]

This is the un-modified code set. Note the re-set code at the bottom. JSW

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:A2")
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 = 27

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

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

'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:A2")
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 = 20

'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