Flashing Cells

@Peter Coombs
See https://drive.google.com/file/d/1qDMNcybXYJ_Qr3VvctsGKYxqTamDKrDj/view
That PDF document describes code to flash the active cell every second. To modify the rate, adjust the following statement:
NextTime = Now + TimeValue("00:00:01")

Hi, I have tried in vain to make the cell to flash? Must be doing something wrong? More important what I am trying to do is make a cell to flash like a metronome. Once I click on the cell I need it to flash to a pre determined value. Firstly can this be done and am I just wasting my time? I do like all that you put up here and it has made it a lot easier to understand,
Thanks,
Peter

@JD
Since you have patiently waited 14 months for an answer, perhaps this will help. Download the following PDF:
https://drive.google.com/file/d/1qDMNcybXYJ_Qr3VvctsGKYxqTamDKrDj/view
It describes a method that is different from the one provided in the Tip. Therefore, remove any VBA copied from the Tip and start over; do not use the "Flashing" style. This method has not been tested on a Mac, but it should work OK in that environment.
Also, see https://sites.google.com/view/MyExcelToolbox/

@ J. Woolley
"Did you review the previous comments before posting your latest?"

I did, though, not being too tech-savvy, I could not follow them much.
I don't understand how to program, but, I am good at following directions :-)))

If you can hold my hand and tell me what to do, step by step, I can do that.

I apologize for my ignorance and for causing you to roll your eyes up in your head!

But getting this to work, in my MacBook using Office for Mac 2016, would really be a plus !

Thanks!

@JD
Did you review the previous comments before posting your latest?

I created an "Excel for Mac 2016" spreadsheet for supply ordering.

The supply order depends on the current stock on hand, which has to be
manually counted.

The spread sheet is rather extensive. Its easy to lose one's place when
taking your eye's off the screen, going to get a count and returning to the
screen. I'm always playing the, "Now where was I?" game - no fun anymore!

It would be great to flash the empty cell (and only that cell) that is awaiting
data entry and after entering the inventory count to then just cursor-down
to the next cell below, have this next cell flash, and the cell (and all cells)
above stop flashing. Only the current "active" cell awaiting data-entry
should flash so when I come back I know just where I left off.

Although an "Excel for Mac 2016" newbie I was able to watch a few short
YouTubes and apply your fix into VBA and get the cell to flash.

It flashed the cell about once per second.

But, when I cursor'd down to the next cell below, in the same column,
not only did this cell not flash, but the cell above continued to flash.
So this is a start, but not over the finish line yet.

How to have the current cell automatically stop flashing after pressing
the cursor down key, or tab key, or enter key after a value is entered,
and have the next cell below start flashing to provide the visual clue
I need to stop playing "Now where was I?". LOL!

I hope I've given you a clearer idea of what I'm looking for.

Also, I tried following your fixes above.

I got through creating and applying the Style to the cells I need to flash.

But I'm stuck on how to create a macro in Excel for Mac 2016.

I have the Developer tab open. I clicked Macros, named it Flash1 but
when I tried to add your macro from above, it won't paste. But MS
Visual Basic opened. I do not know how to work in Visual Basic.

Much appreciated if you can post a detailed walk-through to make
this work!

Thanks!

@Sharif
Open your workbook in Excel, then press Alt+F11 to open the VBE (Visual Basic Editor). You should see the VBAProject frame on the left. Right-click the VBAProject for your workbook and pick Insert > Module. This should open a new Code frame on the right. Copy the macro VBA (Visual Basic for Applications) code into the Code frame. Save the workbook as Macro-Enabled (*.xlsm). Press Alt+F8 to run a macro.
See https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/

Followed everything to

"Now create the macros (there are two of them), as follows: …." where I got lost. What is a macro? Where do I add all that formula.

I can't find a single web site that shows every step.

@Shane
To set the flashing frequency in milliseconds, replace the Tip's VBA code with the following. This assumes you are using Excel 2010 or later (VBA7) and Windows.

Private TimerID As LongPtr
Declare PtrSafe Function SetTimer Lib "user32" _
    (ByVal Hwnd As LongPtr, ByVal nIDEvent As LongPtr, _
    ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr
Declare PtrSafe Function KillTimer Lib "user32" _
    (ByVal Hwnd As LongPtr, ByVal nIDEvent As LongPtr) As Long

Sub Flash_TimerProc _
    (ByVal Hwnd As LongPtr, ByVal wMsg As Long, _
    ByVal idEvent As LongPtr, ByVal dwTime As Long)
    On Error Resume Next
    KillTimer 0, TimerID
    TimerID = 0
    Application.Run "StartFlash"
End Sub

Sub StartFlash()
    With ActiveWorkbook.Styles("Flashing").Font
        If .ColorIndex <> 2 And .ColorIndex <> 3 Then
            .ColorIndex = 3
        End If
        .ColorIndex = 5 - .ColorIndex
    End With
' kill pending Timer, then start a new Timer
    If TimerID <> 0 Then KillTimer 0, TimerID
    Const msec As Long = 500 ' set Timer milliseconds
    TimerID = SetTimer(0, 0, msec, AddressOf Flash_TimerProc)
End Sub

Sub StopFlash()
    If TimerID <> 0 Then KillTimer 0, TimerID
    ActiveWorkbook.Styles("Flashing").Font.ColorIndex = xlAutomatic
End Sub

Adjust the line that begins with Const msec....
Also, see https://sites.google.com/view/MyExcelToolbox/

How can I make this flash faster than a second?

Dear Sir,

I created an "Excel for Mac 2016" spreadsheet for supply ordering.

The supply order depends on the current stock on hand, which has to be
manually counted.

The spread sheet is rather extensive. Its easy to lose one's place when
taking your eye's off the screen, going to get a count and returning to the
screen. I'm always playing the, "Now where was I?" game - no fun anymore!

It would be great to flash the empty cell (and only that cell) that is awaiting
data entry and after entering the inventory count to then just cursor-down
to the next cell below, have this next cell flash, and the cell (and all cells)
above stop flashing. Only the current "active" cell awaiting data-entry
should flash so when I come back I know just where I left off.

Although an "Excel for Mac 2016" newbie I was able to watch a few short
YouTubes and apply your fix into VBA and get the cell to flash.

It flashed the cell about once per second.

But, when I cursor'd down to the next cell below, in the same column,
not only did this cell not flash, but the cell above continued to flash.
So this is a start, but not over the finish line yet.

How to have the current cell automatically stop flashing after pressing
the cursor down key, or tab key, or enter key after a value is entered,
and have the next cell below start flashing to provide the visual clue
I need to stop playing "Now where was I?". LOL!

I hope I've given you a clearer idea of what I'm looking for.

Thanks!

@Keith

I'm not sure if changing styles can be made conditional. It might be available in newer versions of Excel but isn't available in Excel 2010 that I use.

However, if you use the solution that I've put together it absolutely can be conditional and in all versions of Excell (except possibly the Mac versions). In fact if you download the sample workbook (see below) the flashing is conditional on the cell value, trying changing it to 1.

Hi

Could this be expanded to be conditional?

Either via Conditional Formating or through "IF" statements?

If so, now?

Thanks

Keith

@raghavan

If you're using the code in the tip then all you have to do is call the "StartFlash" macro in a routine called Workbook_Open an save it in the ThisWorkbook codepage.

If you use my macros this is already closed.

You do need to check that macro security is set to enable macros in the security settings, but it better to designate the folder where you store the workbook is "trusted".

Hi,

I tried the flashing it worked well. But I need to run the macros each time to start flashing, what i need it as soon as i open my excel sheet one designated cell of my has some information that alone need to flash automatically each time i open without running the macros, is this possible.

@Brian

I've run this using Excel2010 on Win10 with no problem.

So I'm not sure why you're getting the error. I don't think there are any special libraries to be referenced, the OnTime method is standard as I recall.

Barry, I have downloaded the workbook you mentioned and started to run it. It flags the error "Application.ontime is invalid"! I am using Excel 2010 under Windows 7 which is , I thought, one of the valid versions; but the Object Browser confirmed that there is no such property!

@David Randall

Try my version (see the post below dated 20-Jan-20) it doesn't use styles. I haven't tried on a Mac so I'd be interested to know if it works. You download the test file to save you copying the code.

@Brian Hershman

See my alternative solution below, download and try the example spreadsheet. The solution as a matter of course uses conditional formatting so it is only a matter of adding an additional condition to the formula using the AND operator.

Hi Allen, I want to use tip 7223 to CONDITIONALLY format any rows that contains a given value in a given column. I have defined the flashing style and tried it out:
1. as an absolute format it does seem to work, although it needs brighter colours to make it noticeable.
2. But I cannot see how to use it in/as a conditional format.

If you have already discussed this problem elsewhere, please just refer me to the relevant link.

Many thanks,
bsh53g@gmail.com
Brian H.

You may need to ensure that spreadsheets with flashing cells or text (or anything else for that matter) are not going to cause seizures in those with epilepsy. There may be regulations or laws in your jurisdiction which control flashing features especially where spreadsheet may be publicly viewed.

Hello,

I tried following your guidance on how to create flashing cells but it was not possible to do it. I have the Mac version of MS Office and the latest version of Excel I have is 16.32 which does allow permit creation of new styles.

Can you advise how to can create Flashing Cell with current software I have?

Best wishes

David

I have a spreadsheet that does this albeit in Excel 2010, but I've no reason to believe this won't work in Excel 2013 or 2016.

I use a very simple macro that toggles the value of a "Name" once per second. Then I use conditional formatting to either highlight the target cell or not dependent on the value in the Name.
NB a Name doesn't have to refer to a Named range which it is most commonly used for but can hold variables, or even formulas.

In a code "Module":

Public HighlightTime As Date

Sub RefreshHighlight()

If ThisWorkbook.Names("Flash") = "=1" Then
ThisWorkbook.Names("Flash").Value = "=0"
Else
ThisWorkbook.Names("Flash").Value = "=1"
End If

HighlightTime = Now + TimeValue("00:00:01") 'SetTime for +1 secs
Application.OnTime HighlightTime, "RefreshHighLight"
End Sub

and on the "ThisWorkbook" codepage:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime HighlightTime, "RefreshHighLight", , False
End Sub

Private Sub Workbook_Open()
ThisWorkbook.Names.Add "Flash","0"
RefreshHighlight
End Sub

Then in any cell you want to flash; use the conditional formatting formula: "=Flash" and whatever formatting you desire. If you want other conditions as well, say, to only flash above a certain value then use the AND function e.g. "=AND(A1>10, Flash)" to flash for cell values over 10.

You can download a spreadsheet with these macros in them from: http://bit.ly/24thJnc Note: you will have to make sure macros are enabled for this to work as Excel by default is suspicious of any macro enabled workbook sourced from the Internet.

NOTE: the placement of the macro on the specified codepages/modules it important.
and the use of these macros clears the "Undo" stack.

Would it make sense to just place the StartFlash and StopFlash macros in my Personal.XLSB so that I can get flashing anywhere I use the Flashing style?