Flickering spreadsheet due to VBA codes

Malcolm torishi

Board Regular
Joined
Apr 26, 2013
Messages
219
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
.
Running your macro here, only the cursor tends to "flicker". Everything else is fine.

???
 
Upvote 0
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?.
 
Upvote 0
.
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.
 
Upvote 0
.
One thing you might attempt is to change the frequency in which the time is displayed in cell Z4.

You could edit this line :

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


to update the time every 10 seconds like so :

Code:
[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.
 
Upvote 0
Try using the SetTimer API to avoid the screen flickering.
 
Last edited:
Upvote 0
In a "Countdown" timer workbook, I solved by putting it in a TextBox instead of having it in cells.
Code:
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.
 
Upvote 0
This worked for me :

In a Standard Module:
Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top