Flickering spreadsheet due to VBA codes

[Malcolm torishi]

I have the following Module that inserts a ticking clock, with the current time , on to a worksheet that is used to send automated emails once past a certain time. I also have a VBA Code that takes photos from my desktop and inserts them into the same spreadsheet based on the name of the saved phot name. Now what’s happen when both of these codes work together the whole work book, that has command buttons on the spreadsheet and the photos that have been inserted, starts to flickering. If I switch the module clock code off, “sub disable” below it does not flicker. The code for the clock is as below. Does any know why the spreadsheet flickers and is there anything that can be done to stop this flickering. I have tried to insert
Application.ScreenUpdating = False
Application.ScreenUpdating = True
at the beginning and end of VBA Code that insert the photos. But it’s not helped
If anyone could help it would be much appreciated, thank you


Dim SchedRecalc As Date
Sub Recalc()
With Sheet1.Range("Z4")
.Value = Format(Time, "hh:mm:ss AM/PM")
End With
Call Settime
End Sub


Sub Settime()
SchedRecalc = Now + TimeValue("00:00:01")
Application.OnTime SchedRecalc, "Recalc"
End Sub


Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False
End Sub

 

[Logit]

.
Running your macro here, only the cursor tends to "flicker". Everything else is fine.

???

 

[Malcolm torishi]

Sorry not sure what you are trying to tell me. Is the flicker due to the link between the spreadsheet and file on my desktop where the inserted photos come from?.

 

[Logit]

.
I have found it to be very common as the time changes each second in this type timer, the
mouse cursor that is seen on your computer tends to flicker once. Sometimes it is almost not
able to be seen and in other circumstances the user may see the mouse cursor almost
disappear for a split second then reappear each second.

Hope that description is more understandable.

 

[Logit]

.
One thing you might attempt is to change the frequency in which the time is displayed in cell Z4.

You could edit this line :

[COLOR=#333333]SchedRecalc = Now + TimeValue("00:00:01")[/COLOR]

to update the time every 10 seconds like so :

[COLOR=#333333]SchedRecalc = Now + TimeValue("00:00:10")[/COLOR]

The timer will still keep accurate account of the passing time except it will update the time display in cell
Z4 once every 10 seconds. So only every ten seconds should your screen flicker.

 

[Jaafar Tribak]

Try using the SetTimer API to avoid the screen flickering.

 

[Malcolm torishi]

Thank you both

 

[jolivanes]

In a "Countdown" timer workbook, I solved by putting it in a TextBox instead of having it in cells.

Sheets(1).Shapes("TextBox 1").TextFrame.Characters.Text = [A6] & " Days, " & [B8] & " Hours, " & [B11] & " Min and " & [B14] & " Seconds"

Cells A6, B8, B11 and B14 have the calculations for Days, Hours, Minutes and Seconds.

 

[Jaafar Tribak]

This worked for me :

In a Standard Module:

Option Explicit

[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function SetTimer Lib "user32" (ByVal hWnd As LongPtr, ByVal nIDEvent As LongPtr, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr
    Private Declare PtrSafe Function KillTimer Lib "user32" (ByVal hWnd As LongPtr, ByVal nIDEvent As LongPtr) As Long
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
    Private Declare Function SetTimer Lib "user32" (ByVal hWnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
    Private Declare Function KillTimer Lib "user32" (ByVal hWnd As Long, ByVal nIDEvent As Long) As Long
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  If


    
Sub Recalc()
    On Error Resume Next
    With Sheet1.Range("[COLOR=#333333]Z4[/COLOR]")
        .Value = Format(Time, "hh:mm:ss AM/PM")
    End With
End Sub

Sub Settime()
    SetTimer Application.hWnd, 0, 1000, AddressOf Recalc
End Sub

Sub Disable()
    KillTimer Application.hWnd, 0
End Sub

Sub StopTimer()
    KillTimer Application.hWnd, 0
End Sub

Sub Auto_Close()
    Call Disable
    MsgBox "ter"
End Sub