View original (French)

Cell Blinking

Solved
CALI -  
eric2027 Posted messages 296 Status Member -
Hello,

I have a calendar with birthdays to celebrate for which a macro colors them, and I would like those to blink when it's the big day.

Thank you in advance for your help.

Best regards,

23 answers

  • 1
  • 2
  1. Le Pingou Posted messages 12274 Registration date   Status Contributor Last intervention   1 476
     
    Hello,
    Take a look here.

    --
    Best regards.
    The Penguin
    0
    1. CALI
       
      Hello,

      Thank you, but that doesn't help me much. Can you create this macro in my file?

      Thank you.
      0
  3. Le Pingou Posted messages 12274 Registration date   Status Contributor Last intervention   1 476
     
    Hello CALI,
    I think you should take a detailed look at Mike-31's proposal (regards) and then you just need to adapt the spreadsheet according to your needs, everything is there and in a nice way.

    --
    Regards.
    The Penguin
    0
    1. CALI
       
      Hello again,

      I looked at it, but it's all Greek to me.
      Can you do me a favor and adapt its macro to my file?

      Thank you in advance
      Best regards,
      0
  4. cs_Le Pivert Posted messages 8437 Status Contributor 730
     
    Hello,

    Here is an example:

    http://www.cjoint.com/c/EHBjkGUh1GQ

    --
    @+ Le Pivert
    0
  6. CALI
     
    re,

    I'm sorry, but I can't adapt this macro to my file. I know I'm not very skilled, so I really need help.

    Thank you
    0
  7. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Hi,

    if you could start by making your anonymized file available to us, it might be possible to adjust a code
    to attach your file from this link
    https://www.cjoint.com/
    --
    See you,
    Mike-31

    A period of failure is a perfect time to sow the seeds of knowledge.
    0
  8. CALI
     
    http://www.cjoint.com/c/EHBlKyOi3Ai

    Thank you for your cooperation.
    0
  9. cs_Le Pivert Posted messages 8437 Status Contributor 730
     
    Your link is inactive. You should register, it's free. You would receive messages when someone replies to you. The links are blue and clickable.

    Here’s another example that fits all the binders:

    Press Alt F11 to access the editor.

    The codes are in module1 and ThisWorkbook

    http://www.cjoint.com/c/EHBpDuRogDQ

    --
    @+ The Woodpecker
    0
    1. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
       
      Hello Le Pivert,

      the link is good, but the dates are in comments on a calendar. I haven't had the time to analyze the code, which I'll look at this evening. I don't think it's straightforward!
      Best regards
      0
    2. cs_Le Pivert Posted messages 8437 Status Contributor 730
       
      Hi Mike-31,

      I can't activate the link.

      A starting point for the comments: 

      Option Explicit Private Sub Workbook_Open() Dim PremCell As String Dim Cell As Range Dim maligne As String Dim madate As Date Dim com As String madate = Format(Date, "d/mm/yyyy") Set Cell = Cells.Find(madate) On Error Resume Next If Not Cell Is Nothing Then PremCell = Cell.Address maligne = Cell.Address(RowAbsolute:=False) 'gives the column and row number e.g.: $G16 Range(maligne).Select 'selects the cell where the date is com = ActiveCell.Comment.Text End If If com = "" Then Else InitFlash End If End Sub


      Best regards
      0
  10. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Re,

    here is the CALI file

    https://www.cjoint.com/c/EHBrxlogKGj

    --
    A+
    Mike-31

    A period of failure is an ideal time to sow the seeds of knowledge.
    0
  11. CALI
     
    Hello and thank you both for your patience.
    I will try to manage with your send but if you can correct my file, that would be great.

    Best regards,
    0
  12. cs_Le Pivert Posted messages 8437 Status Contributor 730
     
    Here is the modified spreadsheet. I put a dummy birthday for today to check if it's working properly:

    http://www.cjoint.com/c/EHCjPvWkD5Q

    Mike-31, there’s definitely a way to simplify the code.

    It wasn't recognizing the column numbers!

    If you could take a look at it

    Best regards to both of you

    --
    @+ Le Pivert
    0
  13. CALI
     
    Hello,

    Sincerely thank you, I tested it and it's great.
    Thanks to both of you

    Best regards,
    0
  14. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Re,

    Sorry for the delay, connection problem, thanks to Pivert for following up, but if you pass by here again

    you could keep it simple by displaying the comment of the current day, or if you prefer the blinking option, make the comment blink
    to test, paste this code into a module and link the Comman and ArretClign codes to a button or in the ThisWorkbook for automatic launch 

    Option Explicit
    Dim Timer As Date
    Dim Cell As Variant

    Sub Comman()
     For Each Cell In Range("A5:L35")
     If Not (Cell.Comment Is Nothing) And Cell = Date Then 'MsgBox Cell.Comment.Text
     Cell.Select
     Clign
     End If
     Next
    End Sub

    Sub Clign()
    On Error Resume Next
     Timer = Now() + TimeValue("00:00:01") 'the blinking time
     Application.OnTime Timer, "Clign"
     'Show the alert or make it disappear (alternatively)
     With ThisWorkbook
     'Comment
     With .Sheets("Calendrier").Range(ActiveCell.Address)
     .Comment.Visible = Not .Comment.Visible
     End With
     End With
    End Sub

    Sub ArretClign()
    On Error Resume Next
     Application.OnTime Timer, "Clign", , False
     'Hide the alert
     Sheets("Calendrier").Range(ActiveCell.Address).Comment.Visible = False
    End Sub


    --
    A+
    Mike-31

    A period of failure is a perfect time to plant the seeds of knowledge.
    0
  15. cs_Le Pivert Posted messages 8437 Status Contributor 730
     
    Good approach, thank you for this solution.

    I modified it to better fit the request:

    in a module: 

    Option Explicit Dim Timer As Date Dim Cell As Variant Sub Comman() For Each Cell In Range("A5:L35") If Not (Cell.Comment Is Nothing) And Cell = Date Then 'MsgBox Cell.Comment.Text Cell.Select Clign End If Next End Sub Sub Clign() On Error Resume Next Static i i = i + 1 'as long as the chosen number of flashes is not reached '(here 5) we continue If i <= 5 Then Timer = Now() + TimeValue("00:00:01") 'the flashing time Application.OnTime Timer, "Clign" 'Show alert or hide it (alternately) With ThisWorkbook 'Comment With .Sheets("Calendrier").Range(ActiveCell.Address) .Comment.Visible = Not .Comment.Visible End With End With Else Application.OnTime Timer, "Clign", , False 'Hide the alert Sheets("Calendrier").Range(ActiveCell.Address).Comment.Visible = True i = 0 End If End Sub


    and in the sheet: 

    Option Explicit Dim f, ligne, cmt Dim mois, jour Private Sub Worksheet_Activate() Application.ScreenUpdating = False [A5:L35].ClearComments [A5:L35].Interior.ColorIndex = xlNone Set f = Sheets("evenement") For ligne = 2 To f.[A65000].End(xlUp).Row cmt = f.Cells(ligne, 1) & " " & f.Cells(ligne, 2) & " " & f.Cells(ligne, 4) mois = Month(f.Cells(ligne, 3)) jour = Day(f.Cells(ligne, 3)) With Sheets("calendrier").Cells(jour + 4, mois) If .Comment Is Nothing Then .AddComment .Comment.Text Text:=cmt .Comment.Shape.TextFrame.AutoSize = True .Interior.ColorIndex = 3 .Comment.Visible = True End With Next ligne Comman 'starts the flashing Application.ScreenUpdating = True End Sub


    Best regards
    --
    @+ Le Pivert
    0
  16. CALI
     
    Hello to both of you and a big thank you again.

    I will test this morning and keep you updated.

    Best regards,
    0
  17. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Hi,

    Thank you for your reply. It is also possible to control the conditional formatting within the same code and choose to either make the cell or the comment blink, or make the cell blink and display the comment of the relevant cell. And if you want to pique curiosity, you can play a song like Happy_Birthday.

    --
    See you,
    Mike-31

    A period of failure is a perfect time to plant the seeds of knowledge.
    0
    1. eric2027 Posted messages 296 Status Member 6
       
      Hi Mike,

      I would love to explore this curiosity with music, that would be really cool.
      Best regards
      0
    2. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
       
      It's the start of the school year, so things are a bit hectic, but I'm putting together a file that I'll upload to the discussion soon.
      0
    3. eric2027 Posted messages 296 Status Member 6
       
      ok that's really nice of you
      0
  18. CALI
     
    Ok, I'll see, but for the music it's not possible, no sound card, too bad.
    Thank you
    0
  19. cs_Le Pivert Posted messages 8437 Status Contributor 730
     
    Hello,

    While waiting for Mike_31's

    http://www.cjoint.com/c/EHFqISKbGRQ

    --
    See you, Le Pivert
    0
  21. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Hi,

    here is the file I think is operational, feel free to share your feedback.
    currently, the file does not contain any comments, you just need to enter them on the desired dates. Currently, the audio file is intended for noting the birthdays.
    the procedure starts automatically when opening the file, when changing tabs, when changing the date, or by using the single button that changes appearance depending on the ongoing procedure
    in all cases, the procedure can be stopped with the button or by leaving the sheet
    the code runs partially or entirely if today's cell contains a comment
    download the ZIP folder and then extract the Anniv folder before testing the Classeur2 file that you can rename
    https://www.cjoint.com/c/EIci1gbUZjj
    --
    See you later
    Mike-31

    A period of failure is a perfect time to sow the seeds of knowledge.
    0
    1. eric2027 Posted messages 296 Status Member 6
       
      He asks me, 'the file must be in the same directory, or specify its location.'
      0
    2. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
       
      You need to keep the Excel file and the audio file in the same directory; just unzip the folder, and everything should work.
      0
    4. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
       
      Re,

      In the folder file, you have two files, the Excel file named Classeur2.xlsm and the audio file named Happy_Birthday.mp3. It is essential that both files are at the same level in the hierarchy, in other words, in the same directory or the file path needs to be reviewed, and this message points out that inconsistency.

      Place both files in the same location and everything should work, as proof Cali had no issues.
      0
    5. eric2027 Posted messages 296 Status Member 6
       
      I did what you told me, I have no more messages but I have no sound.
      0
  • 1
  • 2