Tweet
Flashing Text In Excel
Suppose you want cell A1 to blink. Select Tools | Macro | Visual Basic Editor from the menu. Right-click the VBAProject item in the tree at left and choose Insert | Module from the pop-up menu. Now type and paste this text into the module:
Public NextFlash As Double
Public Const FR As String = "Sheet1!A1"
Sub StartFlashing()
If Range(FR).Interior.ColorIndex = 3 Then
Range(FR).Interior.ColorIndex = xlColorIndexNone
Else
Range(FR).Interior.ColorIndex = 3
End If
NextFlash = Now + TimeSerial(0, 0, 1)
Application.OnTime NextFlash, "StartFlashing", , True
End Sub
Sub StopFlashing()
Range(FR).Interior.ColorIndex = xlColorIndexNone
Application.OnTime NextFlash, "StartFlashing", , False
End Sub
Arrange the VBA window and the Excel window so you can see cell A1 in Sheet1, then click within the StartFlashing macro, and press F5 to run it. The cell flashes! Click within StopFlashing and press F5 to stop the flashing.
How does it work? First, the constant FR defines the range that will flash; you can make different cells flash by changing its value. If the flash range has a red background, this macro sets it back to normal; if not, it makes the background red. Then it sets itself up to be called again in one second using the application's OnTime method.
The TimeSerial VBA function returns a numeric time-value corresponding to the number of hours, minutes, and seconds passed to it as input. TimeSerial takes only whole numbers, so the shortest time period it can calculate is the one second we use here. We set the NextFlash variable to the current time plus one second, and we call the application object's OnTime method to launch StartFlashing again at that time. Each call to the macro toggles the background from red to normal or vice versa.
The StopFlashing macro simply restores the normal background and calls OnTime to cancel the pending event. To cancel an event, you have to pass the exact same time that was used to schedule it, which is why we had to store that time in the public variable available to both macros.
It's important to cancel all pending events when the workbook is closed, so we'll add an automatic macro to take care of that. And it should also start flashing automatically when the workbook is opened. In the tree at left of the VBA Editor, double-click the ThisWorkbook module and enter or Copy/Paste this text:
Private Sub Workbook_Close()
StopFlashing
End Sub
Private Sub Workbook_Open()
StartFlashing
End Sub
Finally choose Close and Return to Microsoft Excel from the VBA Editor' File menu, and then save the workbook. That's a lot of work just to get flashing text, but it does the job. And of course, you can apply it to any range you like by changing the value of the FR constant.
Comment