• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to get a cell to flash.

Eloise T

Active Member
I would like to get a cell to flash to (obviously) draw attention to that cell for colleagues that peruse a particular spreadsheet only on occasion and frequently miss vital information.

I found the following Excel file on a web site (see link in the attached file).

In this case, I need the cell to do nothing if the cell B3 contains "GOODBYE" and flash if the cell contains "HELLO."

Thanks for your assistance!
 

Attachments

  • FLASHING.xlsm
    18.2 KB · Views: 7
Try this code (in standard module)
Code:
Public NextFlash As Double
Const FlashRng As String = "Sheet1!B3"

Sub StartFlashing()
    If Range(FlashRng).Value = "HELLO" Then
        If Range(FlashRng).Interior.ColorIndex = 7 Then
            Range(FlashRng).Interior.ColorIndex = xlNone
        Else
            Range(FlashRng).Interior.ColorIndex = 7
        End If

        NextFlash = Now + TimeSerial(0, 0, 1)
        Application.OnTime NextFlash, "StartFlashing", , True
    ElseIf Range(FlashRng).Value = "GOODBYE" Then
        Exit Sub
    End If
End Sub

Sub StopFlashing()
    Range(FlashRng).Interior.ColorIndex = xlNone
    Application.OnTime NextFlash, "StartFlashing", , False
End Sub
 
This is what is happening:
STEP 1. Opened spreadsheet.
upload_2018-1-8_11-7-43.png



STEP 2. What I see after depressing the "Start Flashing" button:
upload_2018-1-8_11-9-8.png



STEP 3. What I see after depressing the OK button followed by the "Stop Flashing" button:
upload_2018-1-8_11-10-53.png

I'm using Excel 2016 if it matters in this situation.
Thank you.
 
Have a look at this file
It worked! Thanks. Now I'll go back to figure out the difference between the two files.

BTW, is there a way to have the VBA module to start automatically when the file is opened, otherwise, I'm going to have to make a "CLICK HERE TO START" button for the user so they are coerced to start the macro, correct?
 
You can run the StartFlashing procedure in workbook open event in this way
Code:
Private Sub Workbook_Open()
    Call StartFlashing
End Sub
Is there some protocol I need to adhere to as I am unable to save the spreadsheet with the:
Code:
Private Sub Workbook_Open()
  Call StartFlashing
End Sub

inserted in the file?
Code:
Option Explicit

Public NextFlash As Double
Const FlashRng As String = "Sheet1!B3"

Sub StartFlashing()
    If Range(FlashRng).Value <> "" Then
        If Range(FlashRng).Interior.ColorIndex = 7 Then
            Range(FlashRng).Interior.ColorIndex = xlNone
        Else
            Range(FlashRng).Interior.ColorIndex = 7
        End If

        NextFlash = Now + TimeSerial(0, 0, 1)
        Application.OnTime NextFlash, "StartFlashing", , True
    ElseIf Range(FlashRng).Value = "GOODBYE" Then
        Exit Sub
    End If
End Sub

Sub StopFlashing()
    Range(FlashRng).Interior.ColorIndex = xlNone
    Application.OnTime NextFlash, "StartFlashing", , False
End Sub

Private Sub Workbook_Open()
    Call StartFlashing
End Sub
 
Hi ,

All event procedures have to reside in the location where the corresponding event is triggered.

For instance a Worksheet_Change procedure has to be placed in the worksheet section where the event will be triggered.

A Workbook_Open procedure has to be placed in the ThisWorkbook section.

See the attached file.

Narayan
 

Attachments

  • Test.xlsm
    17.1 KB · Views: 17
Hi ,

All event procedures have to reside in the location where the corresponding event is triggered.

For instance a Worksheet_Change procedure has to be placed in the worksheet section where the event will be triggered.

A Workbook_Open procedure has to be placed in the ThisWorkbook section.

See the attached file.

Narayan
I read your words, but the point didn't sink in, especially since I didn't see a Sub Call:

Private Sub Workbook_Open()
Call StartFlashing
End Sub

or Worksheet_Change or Workbook_Open in the code...

...and yet it worked of course.


One other thing, if I need to spread the affected area over an array instead of a single cell how would I change this?

currently: Const FlashRng As String = "Sheet1!B5"

Const FlashRng As String = "Sheet1!B5:B10" didn't work.

Thank you again.
 
You can run the StartFlashing procedure in workbook open event in this way
Code:
Private Sub Workbook_Open()
    Call StartFlashing
End Sub
What is the rationale for making the Sub private? Is it so you'll be the only one who knows it's there? ...or is there a better reason?....just curious.
 
Last edited:
The idea of using Workbook Open Event is to run specific macro at the opening of the workbook .. as I got your question
I feel like I'm on the cusp of understanding this, but the last VBA macro is throwing me. Your code and Nayaran's last code, TEST.xlsm, are identical, but his "auto-starts" and yours does not. What is the difference? I expected to see:

Code:
Private Sub Workbook_Open()
    Call StartFlashing
End Sub
...which I could not get to work,

or Worksheet_Change or Workbook_Open imbedded in the macro but it is not.
The current code:

Code:
Option Explicit

Public NextFlash As Double
Const FlashRng As String = "Sheet1!K4"

Sub StartFlashing()
    If Range(FlashRng).Value <> "" Then
        If Range(FlashRng).Interior.ColorIndex = 7 Then
            Range(FlashRng).Interior.ColorIndex = xlNone
        Else
            Range(FlashRng).Interior.ColorIndex = 7
        End If

        NextFlash = Now + TimeSerial(0, 0, 1)
        Application.OnTime NextFlash, "StartFlashing", , True
    ElseIf Range(FlashRng).Value = "GOODBYE" Then
        Exit Sub
    End If
End Sub

Sub StopFlashing()
    Range(FlashRng).Interior.ColorIndex = xlNone
    Application.OnTime NextFlash, "StartFlashing", , False
End Sub

is identical but one auto-starts and the other one, does not.
Sorry, I'm being redundant, but I don't see a difference.

Do you understand my confusion?
 
Eloise T ... okay ...
You have two files - okay?
You would compare like next:
Do both files has same worksheets (including names)?
Then You'll open both files views where You can see those codes.
Are those codes saved exact same 'place'?
Screen Shot 2018-01-09 at 19.41.09.png
This time, I call name as 'Sheet1 (Sheet1)' and ThisWorkbook.
Or are some of codes saved other places - Modules ex Module1..?
 
Your picture helped me probe-around the menu bar to try to find where the following picture Workbook lives. This is new territory for me.
I found I can click on the "Window" menu to find this, and that where there is a "Workbook" option as shown by the red arrow.
upload_2018-1-9_14-33-25.png
 

Attachments

  • upload_2018-1-9_14-32-22.png
    upload_2018-1-9_14-32-22.png
    32.4 KB · Views: 3
yes
... if there is still some challenges then
those Your link ... do not work as those would work!
Screen Shot 2018-01-09 at 21.33.08.png
 
That my previous message taken from Your #19 Reply four minutes after You sent it ... now it looks 'better'.
I have tried to ask You to compare those Your files like below:
Screen Shot 2018-01-09 at 22.02.47.png
I haven't done any modifications to those.
NOW, those has differences as You would notice!
I cannot know Your version.
You should compare Yourself Your versions.
 
That my previous message taken from Your #19 Reply four minutes after You sent it ... now it looks 'better'.
I have tried to ask You to compare those Your files like below:
View attachment 48782
I haven't done any modifications to those.
NOW, those has differences as You would notice!
I cannot know Your version.
You should compare Yourself Your versions.

This was the difference (circled in red). I did not know (prior to Replys 7, 9, 18, & 19) about the sneaky back door that you could put a Call to start the macro when the file was opened that was not a part of the main macro code. :eek: As always, THANK YOU for your assistance and patience as well as every one here that contributed, i.e. YasserKhalil and Narayan. This web site has been my sole Excel and VBA source of learning as my employer, at least for now, refuses to pay for any extracurricular Excel or VBA classes. I routinely feel like my job hangs in the balance and your gracious help keeps the wolf at bay.

upload_2018-1-11_10-45-3.png
 
Thank you for the link. I tried to use it but can't get it to "engage." Here is the error message:
upload_2018-1-13_19-12-8.png
which says, "The macro may not be available in this workbook or all macros may be disabled." ...which is not true.

I have a Workbook with multiple tabs. What I'm looking to accomplish is whenever a red Unicode U+221A () (square root symbol) appears in Column K (K3:K5003) of any of the tabs (worksheets of the workbook), the cell containing the , will flash.

As you can see in this blog, there are 3 different options. The following is what I've come up with by combining several options and without success. Any suggestions?

Code:
Public xTime As Double
Sub StartFlashing()
    Dim ws As Worksheet
    Dim xCell As Range
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Formula Info" And ws.Name <> "Next Tech" Then  'skip over these tabs
            Set xCell = Range("K3:K5003")
            With ThisWorkbook.Worksheets("Sheet1").Range("K3:K5003").Font
                If ws.Cells(Rows.Count, "A").End(xlUp).Row > 2 Then
                    If xCell.Font.Color = vbMagenta Then
                        xCell.Font.Color = vbBlue
                    Else
                        xCell.Font.Color = vbMagenta
                    End If
                End If
                    xTime = Now + TimeSerial(0, 0, 1)
                    Application.OnTime xTime, "StartFlashing", , True
          End With
        End If
    Next
End Sub

Public Sub StopFlashing()
    Application.OnTime xTime, "StartFlashing", , False
End Sub


To insert a standard macro you type ALT + F11 then ALT + F7...as you know.

What do you type to get the following into the macro code?

Private Sub Workbook_Open()
Call StartFlashing
End Sub

Once it's there, I can find it by clicking the down arrow on VBA Windows menu, but what I don't know is how to get it there in the beginning.

Thanks!

Have a look at this link as it is the same technique but using the styles so as to be able to apply to any cell by formatting its style
https://excelribbon.tips.net/T007223_Flashing_Cells.html?dn=3&awt_m=JddvzkBQkMQF4X&awt_l=CYlhP[/
 
Last edited:
Back
Top