Combination of Application.OnTime + Locked Session + Userform

[Ploom]

Hi all,

I wrote a small application.ontime to win some time in the morning, which launch a macro before me arriving at my workplace
At the time set by my application.ontime, my computer is opened and only my session is locked

Public Sub StartTimer()
    h_go = Worksheets("FILES").Range("timer_auto").Value
    Application.OnTime h_go, "auto_update", , True
End Sub

Sub auto_update()

Sheets("FILES").Activate
Sheets("FILES").Calculate

Dim current_date_file As Date            
        Call find_datas_inf  'this part of the code is well executed           
        Call launcher 'this is where i think there are some troubles
            
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
                
ExportTool.Show

Application.ScreenUpdating = True
        
MsgBox ("Autoload terminée")       
Application.Calculation = xlCalculationAutomatic   
    
End If 
Call StartTimer
End Sub

My macro runs by itself alone for a part, but the second part with the call launcher, every time, it waits for me to unlock my session before continue running. However i don't want it to wait for me, i would like it to be finished before i arrive

Sub launcher()
UserForm1.Show 'it shows an userform which is used as a progress bar, to know where we are in the process, as it is a pretty long macro
End Sub

Private Sub UserForm_Activate()
UserForm1.LabelProgress.Width = 0
Call Import_Greeks_Infinity
End Sub

And so every time i arrive, whenever it is 8:15 or 8:30 or 8:45, i think the userform pop at the moment i unlock my session or is stuck at 0% until the moment i unlock this session.

If i run this same macro during the day i have no issue at all with it.

I have no clue as what causes that, i have another macro which should run in the same way but also has the same issue. The first part on my macro, which doesn't use an userfom is well exectued and it is important for me to keep the userform to see the progress as it is a macro which takes 15-20mn to run.

I think the pb lies in the userform.show but maybe i'm wrong, if you want to see the first lines of the macro which is called by the userform

Sub Import_Greeks_Infinity()

Sheets("Files").Calculate

    For Each c In Sheets
        c.EnableCalculation = False
    Next c
    
    modcalc = Application.Calculation
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Sheets("FILES").Calculate
    depart = ActiveWorkbook.Name
    
Dim PctDone As Single
PctDone = 0.000001
Namefile = "Chargement"
UpdateProgressBar PctDone, Namefile
    
    'report
    Workbooks(depart).Sheets("FILES").Range("UploadTime").Cells(1, 1) = Workbooks(depart).Sheets("FILES").Range("UploadDateInfinity").Value
    Workbooks(depart).Sheets("FILES").Range("FileUploadDate").Cells(1, 1) = Date
    Workbooks(depart).Sheets("FILES").Range("UploadWrongDates").ClearContents
    Workbooks(depart).Sheets("FILES").Range("UploadedFiles").ClearContents

It is only the first lines but as it is stuck at the start i think it could only concern those lines if it was the case


Thanks for any advices

 

[Jaafar Tribak]

Have you tried using a modeless userform ? UserForm1.ShwoModal = False

Or :

UserForm1.Show vbModeless

 

[Ploom]

Just tried it and still had the same issue
However the strange thing is, with vdmodeless

Call find_datas_inf  'this part of the code is well executed           
Call launcher 'still didn't work
            
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
                
ExportTool.Show 'worked

However
ExportTool.Show works the same "call launcher" should.
I'll try to replace my call launcher by a userform1.show and see if it works this way

 

[Ploom]

Well with userform1.show it still didn't work, i think it might actually come from the code the userform call in itself