excel 2003

[col]

How do I change cell colours rapidly in excel 2003 to create a flashing effect

 

[Mike H]

Hi,

Before you do this consider that:-

1. Some people are sensitive to flashing lighst and it can induce fits.
2. Others may find it so irritating they would simply close the workbook and
delete it and FWIW I fall into the second category

So of you must put this code in a general module

Option Explicit
Public FlashRate As Double

Sub Flash()
If Range("A1").Interior.ColorIndex = 3 Then
Range("A1").Interior.ColorIndex = 6
Else
Range("A1").Interior.ColorIndex = 3
End If
FlashRate = Now + TimeSerial(0, 0, 1)
Application.OnTime FlashRate, "Flash", , True
End Sub

Sub StopBlink()
Range("A1").Interior.ColorIndex = xlAutomatic
Application.OnTime FlashRate, "Flash", , False
End Sub


Mike

 

[Ed O'Brien]

Hi, Mike.

I have a multicoloured, daily record (albeit 2007) used by four or five
others. One particular entry falls into the "flashing cell" type of
requirement to ensure it is completed each time the worksheet is brought up
to date. I have the code you kindly provided which works fine, but what I
would like to achieve is the flashing cell to be active immediately on
opening the workbook rather than having to launch the macro. Can this be
done?

TIA,
Ed

 

[Gord Dibben]

If you MUST have a blinking cell..............

Private Sub Worksheet_Activate()
Flash
End Sub

You will also want to stop the blinking when the cell is filled in.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$A$1" And Target.Value <> "" Then
StopBlink
End If
stoppit:
Application.EnableEvents = True
End Sub

These go into the sheet module.

I would not have blinking cells.

You could do the same with a message box reminder that popped up when user
activates the sheet.

Private Sub Worksheet_Activate()
MsgBox "Don't forget to fill in A1"
End Sub


Gord Dibben MS Excel MVP

 

[Ed O'Brien]

Thanks, Gord.

After a dozen times of the cell being missed and causing on-going errors
later, a flashing cell I hope will cure it.

I have been trying the Auto_Open and VB method shown in "Help" but I can't
get either to work. Manually launching it works okay but hardly an answer.
(Better to fill the dammed cell in than mess with macros)!

The message method may work as long as the dolts don't cancel it and then
forget...!

I'll let you know how I get on.

Ed

 

[Ed O'Brien]

Hi, Gord.

I gave up on the blinking cell idea and decided on the pop-up message, which
is probably better.

I'm running Excel 2007 but can't even get this to work.

The file is .xlsm, all macros are enabled. I clicked "Sheet 1", inserted
"Module 1" and I placed your script -

Private Sub Worksheet_Activate()
MsgBox "Don't forget to fill in A12"
End Sub

It is a blank (test) workbook with no other entries.

I'm mystified. Any ideas?

Thanks for your patience.

Ed

 

[Gord Dibben]

Do not place the sheet event code into an inserted standard module.

Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module.

OR open VBEditor and double-click on the sheet name.


Gord

 

[Ed O'Brien]

Thanks, Gord.

Yes, I did that. As far as I can tell, I've done nothing wrong, but then I
am getting on a bit!

I click the Developer tab. (All macros are enabled under "Macro Security".
The Workbook location is also)
"Trusted".
I click "Visual Basic"
I right click "Sheet 1 (Sheet1)"
I click "View Code"
I copy/paste your script (which I know is accurate).
I have tried with both "Workbook" and "General" headings.
I click the 'Save' icon.

I am using a blank workbook

I have even added a Sheet 2 and tried it n that without success.

Thanks again for your patience.

Ed

 

[Ed O'Brien]

Hi, Gord.

After adding a second sheet, changing worksheets brings up the message box
when a sheet is opened. However, when the book is first opened, it fails. As
there is only one sheet in the workbook in question, the message will never
display. Is there a code to activate the message as soon as the workbook is
opened?

TIA.

Ed

 

[Gord Dibben]

With a one sheet workbook place this code in Thisworkbook module.

Private Sub Workbook_Open()
MsgBox "Don't forget to fill in A12"
End Sub


Gord

 

[Ed O'Brien]

Thanks a million, Gord.

Works fine.

Kind regards,

Ed

 

[Ed O'Brien]

Hi, Gord.

I hope you don't mind me atking up a little more of your time.

Having got this to work fine, now. As a matter of interest - is it possible
to format the message box?

TIA

Ed

 

[Gord Dibben]

No.

You would have to build a customized UserForm and call it up.


Gord

 

[Ed O'Brien]

Thanks, Gord.

Looks a bit over my head, I'm afraid.

Ed