1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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.

Discussion in 'VBA Macros' started by Eloise T, Jan 8, 2018.

  1. Eloise T

    Eloise T Active Member

    Messages:
    688
    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!

    Attached Files:

  2. YasserKhalil

    YasserKhalil Active Member

    Messages:
    953
    Try this code (in standard module)
    Code (vb):
    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
  3. vletm

    vletm Excel Ninja

    Messages:
    3,520
  4. Eloise T

    Eloise T Active Member

    Messages:
    688
    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.
  5. YasserKhalil

    YasserKhalil Active Member

    Messages:
    953
    Have a look at this file

    Attached Files:

  6. Eloise T

    Eloise T Active Member

    Messages:
    688
    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?
  7. YasserKhalil

    YasserKhalil Active Member

    Messages:
    953
    You can run the StartFlashing procedure in workbook open event in this way
    Code (vb):
    Private Sub Workbook_Open()
        Call StartFlashing
    End Sub
  8. Eloise T

    Eloise T Active Member

    Messages:
    688
    Is there some protocol I need to adhere to as I am unable to save the spreadsheet with the:
    Code (vb):
    Private Sub Workbook_Open()
      Call StartFlashing
    End Sub
    inserted in the file?
    Code (vb):
    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
  9. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,561
    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

    Attached Files:

  10. Eloise T

    Eloise T Active Member

    Messages:
    688
    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.
  11. Eloise T

    Eloise T Active Member

    Messages:
    688
    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: Jan 9, 2018
  12. vletm

    vletm Excel Ninja

    Messages:
    3,520
  13. YasserKhalil

    YasserKhalil Active Member

    Messages:
    953
    The idea of using Workbook Open Event is to run specific macro at the opening of the workbook .. as I got your question
    ThrottleWorks likes this.
  14. Eloise T

    Eloise T Active Member

    Messages:
    688
    Last edited: Jan 9, 2018
  15. Eloise T

    Eloise T Active Member

    Messages:
    688
    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 (vb):

    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 (vb):
    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?
  16. vletm

    vletm Excel Ninja

    Messages:
    3,520
    Eloise T
    Are all of those saved to same 'places'?
    ThrottleWorks likes this.
  17. Eloise T

    Eloise T Active Member

    Messages:
    688
    All are saved to the same folder on my HD, if that is what you are asking?
  18. vletm

    vletm Excel Ninja

    Messages:
    3,520
    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..?
  19. Eloise T

    Eloise T Active Member

    Messages:
    688
    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

    Attached Files:

  20. vletm

    vletm Excel Ninja

    Messages:
    3,520
    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
  21. Eloise T

    Eloise T Active Member

    Messages:
    688
    Your last message seems garbled.

    upload_2018-1-9_14-37-32.png
  22. vletm

    vletm Excel Ninja

    Messages:
    3,520
    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.
  23. Eloise T

    Eloise T Active Member

    Messages:
    688
    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
  24. YasserKhalil

    YasserKhalil Active Member

    Messages:
    953
  25. Eloise T

    Eloise T Active Member

    Messages:
    688
    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 (vb):
    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!

    Last edited: Jan 14, 2018

Share This Page