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

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,841
Office Version
  1. 365
Platform
  1. Windows
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

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

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,297
Office Version
  1. 2010
Platform
  1. Windows
bit of a tweak :)
VBA Code:
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'

1615966646329.png
1615966677951.png
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,272
Members
416,963
Latest member
samfuge

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
Top