Combination of Application.OnTime + Locked Session + Userform

Ploom

New Member
Joined
Feb 28, 2016
Messages
28
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

Code:
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 :LOL:

Code:
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
Code:
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

Code:
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

Well-known Member
Joined
Dec 5, 2002
Messages
7,493
Office Version
2016
Platform
Windows
Have you tried using a modeless userform ? UserForm1.ShwoModal = False

Or :
Code:
UserForm1.Show vbModeless
 

Ploom

New Member
Joined
Feb 28, 2016
Messages
28
Just tried it and still had the same issue
However the strange thing is, with vdmodeless

Code:
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

New Member
Joined
Feb 28, 2016
Messages
28
Well with userform1.show it still didn't work, i think it might actually come from the code the userform call in itself
 

Forum statistics

Threads
1,082,044
Messages
5,362,855
Members
400,696
Latest member
Kclynn

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top