Add the date/time into a cell when a UserForm is closed/Hidden

[gheyman]

I have code that keeps the userForm showing until the refreshing of tables is completed. (The refresh takes several minutes and by having the userform up the users dont start clicking all over or think the workbook isnt performing.) The code works perfectly.

But I am trying to figure out a way to add the date and time to a cell after the tables are refreshed so that I can track how long it took. As you can see my code adds the time to cell E23 when the VBA code is intiated (By a button) I though I could use code to add the date when the Form closes (or Hide) but there is no event for "Hide". There is only Terminate or deactivate which I believe is different from UserForm.Hide

Any suggestions are appreciated

Sub RefreshAll()
'G Heyman DRS NIS 210308
'Refresh all the Queries and leave Userform open while queries are updating

UserForm1.Show

    CurrentUser = Environ("UserName")

    Sheets("Home").Unprotect Password:="pa$$word"

    With ThisWorkbook
        'Set Message (Who and when updated) in cells
            Sheets("Home").Range("E23").Value = "Last Refreshed on: " & Now
            Sheets("Home").Range("J23").Value = Now
            Sheets("Home").Range("E25").Value = "Last Refreshed by: " & CurrentUser
        
        'Refresh Tabs
            Sheets("Order Alignment").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
            Sheets("Buyer Status List").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
            Sheets("Comments Log").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
            Sheets("PO_Pending2Open").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
            Sheets("Req2PendingPO").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
            
            ' Refresh Pivot Tables
            For Each pc In ThisWorkbook.PivotCaches
                pc.Refresh
            Next pc
            
    End With

    
    
    Sheets("Home").Protect Password:="pa$$word"
    Sheets("Home").Range("E24").Select

UserForm1.Hide

End Sub

 

[diddi]

bit of a tweak

Sub RefreshAll()
'G Heyman DRS NIS 210308
'Refresh all the Queries and leave Userform open while queries are updating
Dim StartTime
UserForm1.Show
    StartTime = Now

    CurrentUser = Environ("UserName")

    Sheets("Home").Unprotect Password:="pa$$word"

    With ThisWorkbook
        'Set Message (Who and when updated) in cells
            Sheets("Home").Range("E23").Value = "Last Refreshed on: " & Now
            Sheets("Home").Range("E25").Value = "Last Refreshed by: " & CurrentUser
        
        'Refresh Tabs
            Sheets("Order Alignment").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
            LabelProg.width=20
            DoEvents
            Sheets("Buyer Status List").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
            LabelProg.width=40
            DoEvents
            Sheets("Comments Log").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
            LabelProg.width=60
            DoEvents
            Sheets("PO_Pending2Open").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
            LabelProg.width=80
            DoEvents
            Sheets("Req2PendingPO").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
            LabelProg.width=100
            DoEvents
            
            ' Refresh Pivot Tables
            For Each pc In ThisWorkbook.PivotCaches
                pc.Refresh
            Next pc
            LabelProg.width=120
            DoEvents
          
    End With

    
    
    Sheets("Home").Protect Password:="pa$$word"
    Sheets("Home").Range("E24").Select
    LabelProg.caption "Time elapsed is: " & Now = StartTime 'or dump the result in a cell if you like
    Application.Wait(Now + TimeValue("0:00:02"))
    
UserForm1.Hide

End Sub

Add 2 labels to your userform as below and you get a free progress bar without messing up your userform with multiple windows to deal with. call coloured label 'LabelProg'