Excel Reopens

anichols

Board Regular
Joined
Mar 11, 2021
Messages
67
Office Version
  1. 365
Platform
  1. Windows
Hello all,


I am having an issue where this workbook will reopen after closing. I've googled many different solutions, and still can't get it to stop. The idea behind this is that if the workbook is inactive, then a userform will pop up informing them that they have 90 seconds to confirm they are still in the workbook or it will close the workbook. The User form will call "closeit" sub to close the workbook. Clicking the button on the userform will type a value into the intro sheet which will update the formulas in B1 to give a new time before the sheet can be considered "inactive". (Also for testing purposes, macro_timer will run every 2 minutes, though it will be every 30 in the finished product.) I should note that it does work perfectly (except that it reopens, lol)
Any help would be greatly appreciated!!


VBA Code:
Public interval As Double
Sub macro_timer()
interval = Now + TimeValue("00:02:00")
Application.OnTime interval, "my_macro"
End Sub
Sub my_macro()
Dim Today
'Dim oldsheet As String
Today = Now
'---------------------INSERT FILE NAME HERE------------------------------------------
With Workbooks("PPP Play test")
'oldsheet = ActiveSheet.Name
Workbooks("PPP Play test").Activate
Sheets("INTRO").Visible = True
Sheets("INTRO").Select
If Today > Range("B1").Value Then
    WakeUpCall.Show ([False])
Else
End If
'Sheets(oldsheet).Select
Sheets(1).Select
Sheets("INTRO").Visible = xlSheetVeryHidden
Call macro_timer
End With
End Sub
Sub stop_macro()
Application.OnTime Earliesttime:=interval, Procedure:="my_macro", Schedule:=False
End Sub

Sub closeit()
'Dim oldsheet As String
'---------------------INSERT FILE NAME HERE------------------------------------------
With Workbooks("PPP Play test")
    'oldsheet = ActiveSheet.Name
    Workbooks("PPP Play test").Activate
    If Sheets("INTRO").Range("A2").Value = "1" Then
    Application.ScreenUpdating = False
    Sheets("INTRO").Visible = True
    Sheets("INTRO").Select
    Range("A2").Select
    ActiveCell.FormulaR1C1 = ""
    Range("A4").Select
    'Sheets(oldsheet).Select
    Sheets("INTRO").Visible = xlSheetVeryHidden
    Application.ScreenUpdating = True
    Unload WakeUpCall
    Else
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    End If
End With
End Sub
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
You don't stop the timer from running. Even if you close the workbook, the OnTime procedure will still open at the 2 minute mark to run the code.

Right before your ActiveWorkbook.Save or .Close lines, add a call to the stop_macro Sub.

VBA Code:
    Else
    stop_macro
    ActiveWorkbook.Save
    ActiveWorkbook.Close
 

anichols

Board Regular
Joined
Mar 11, 2021
Messages
67
Office Version
  1. 365
Platform
  1. Windows
You don't stop the timer from running. Even if you close the workbook, the OnTime procedure will still open at the 2 minute mark to run the code.

Right before your ActiveWorkbook.Save or .Close lines, add a call to the stop_macro Sub.

VBA Code:
    Else
    stop_macro
    ActiveWorkbook.Save
    ActiveWorkbook.Close
Thank you! That does help for the autoclose, but when the user closes the file, I'm facing the same issue. When the user form opens, I have this code:
VBA Code:
Sub UserForm_Initialize()
Application.OnTime Earliesttime:=Now + TimeValue("00:01:30"), _
                   Procedure:="LostArk.closeit", _
                   Schedule:=True
End Sub
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
    Sheets("INTRO").Visible = True
    Sheets("INTRO").Select
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A4").Select
    Sheets("INTRO").Visible = xlSheetVeryHidden
Application.ScreenUpdating = Trues
Application.Run "LostArk.macro_timer"
Unload Me
End Sub



I'm assuming that the Application.OnTime keeps repeating? Not sure why though.

Just for additional info, here's the before close code.
VBA Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
Sheets("INTRO").Visible = True
Sheets("INTRO").Select
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> ThisWorkbook.ActiveSheet.Name Then
ws.Visible = xlSheetHidden
End If
Next ws
Unload WakeUpCall
Application.Run "LostArk.stop_macro"
'Unload interval
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub
 

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
In the Initialize code, you set another timer to run the "closeit" Sub. If the button is clicked, the "macro_timer" timer starts. So now you have two timers running. Maybe the closeit timer is still running even though the macro_timer is stopped if the closeit sub is run manually sooner. Does the workbook stop opening if you add stopping the closeit timer to the "stop_macro" code so they are both turned off?
 

anichols

Board Regular
Joined
Mar 11, 2021
Messages
67
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Good Catch! That helped, though I had to tweak my User form initialize code. I am still having issues. Both issues only come up when there are other workbooks open. Here's the updated code:
VBA Code:
Public interval As Double


Sub UserForm_Initialize()
'Application.OnTime Earliesttime:=Now + TimeValue("00:00:30"), _
                   Procedure:="LostArk.closeit", _
                   Schedule:=True  'old code
                   
interval = Now + TimeValue("00:00:30")
Application.OnTime interval, "LostArk.closeit"
                   
End Sub
1st, after opening and before the userform ever pops up, the user would close the excel sheet like normal leaving whatever other workbooks open, then it would reopen and reclose, immediately. Really if the computer is fast, not noticeable, but I have lots of people with slow computers.

2nd issue: after the userform has popped up and the user has delayed the workbook close. the user would close the excel sheet like normal leaving whatever other workbooks open, then it would reopen and reclose, immediately. then reopen and reclose a 2nd time, then reopen a third time and pop an error.

my stop macro is updated to this:
VBA Code:
Sub stop_macro()
On Error Resume Next
Application.OnTime Earliesttime:=interval, Procedure:="my_macro", Schedule:=False
Application.OnTime Earliesttime:=interval, Procedure:="closeit", Schedule:=False 'new
End Sub

So I have moved in the correct direction, as now when the workbook is the only book open, it will close and not reopen every time. But I can't figure out why it'll keep trying to reopen if I'm stopping the timers in the Before Workbook Close.
 

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
Let's get back to the basics of what I think you want to do.

Every 30 minutes (30 second in the testing mode) you want the WakeUpCall form to show in order for the user to confirm he is still working on the file. When the WakeUpCall form shows, the user has 90 seconds (10 seconds in testing mode) to verify that there is still interest. Clicking the button will reset the time for another 30 minutes before the inactivity check is performed again. Doing nothing with the form will cause the closeit procedure to run.

(I'm not sure what the value in B1 has to do with the 30 minute timer, though)

So let's get started:

Somehow (Workbook_Open event, etc.) the macro_timer procedure is run to start the inactivity clock (30 mins) running. When the OnTime timer is ended, the my_macro procedure opens the WakeUpCall form (if the time is greater than B1), which starts the 90 second form timer and also resets the 30 min OnTime timer by running macro_timer again. If the user clicks the button in WakeUpCall, the 90 second timer can get cancelled in addition to whatever else is going on with Sheets("INTRO"). Since the 30 minute timer has been re-started, the process repeats. If no button is clicked, the 90 second timer runs and calls the closeit procedure, which needs to stop the my_macro timer and do the rest of the stuff depending on Range("A2").

Code for WakeUpCall
VBA Code:
Dim CloseItInterval As Double

Private Sub CommandButton1_Click()
    Application.OnTime CloseItInterval, "LostArk.closeit", , False
    Application.ScreenUpdating = False
    Sheets("INTRO").Visible = True
    Sheets("INTRO").Select
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A4").Select
    Sheets("INTRO").Visible = xlSheetVeryHidden
    Application.ScreenUpdating = True
    Unload Me
End Sub

Sub UserForm_Initialize()
    CloseItInterval = Now + TimeValue("00:00:10")
    Application.OnTime CloseItInterval, "LostArk.closeit"
End Sub

LostArk module code:
VBA Code:
Dim interval As Double

Sub macro_timer()
    stop_macro
    interval = Now + TimeValue("00:00:30")
    Application.OnTime interval, "my_macro"
End Sub

Sub my_macro()
    Dim Today As Double
    Today = Now
    With Workbooks("PPP Play test")
        Workbooks("PPP Play test").Activate
        Sheets("INTRO").Visible = True
        Sheets("INTRO").Select
        If Today > Range("B1").Value Then
            WakeUpCall.Show ([False])
        End If
        Sheets(1).Select
        Sheets("INTRO").Visible = xlSheetVeryHidden
        macro_timer
    End With
End Sub

Sub stop_macro()
    On Error Resume Next
    Application.OnTime Earliesttime:=interval, Procedure:="my_macro", Schedule:=False
End Sub

Sub closeit()
    Unload WakeUpCall
    stop_macro
    With Workbooks("PPP Play test")
        Workbooks("PPP Play test").Activate
        If Sheets("INTRO").Range("A2").Value = "1" Then
            Application.ScreenUpdating = False
            Sheets("INTRO").Visible = True
            Sheets("INTRO").Select
            Range("A2").Select
            ActiveCell.FormulaR1C1 = ""
            Range("A4").Select
            Sheets("INTRO").Visible = xlSheetVeryHidden
            Application.ScreenUpdating = True
        Else
            ActiveWorkbook.Save
            ActiveWorkbook.Close
        End If
    End With
End Sub

I don't understand the interplay between the form button setting Range("A2") to be 1 and then not saving/closing the workbook if the user fails to click the button that forces the closeit procedure to run. However, that's not important to getting the timers to run.
 

anichols

Board Regular
Joined
Mar 11, 2021
Messages
67
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Let's get back to the basics of what I think you want to do.

Every 30 minutes (30 second in the testing mode) you want the WakeUpCall form to show in order for the user to confirm he is still working on the file. When the WakeUpCall form shows, the user has 90 seconds (10 seconds in testing mode) to verify that there is still interest. Clicking the button will reset the time for another 30 minutes before the inactivity check is performed again. Doing nothing with the form will cause the closeit procedure to run.

(I'm not sure what the value in B1 has to do with the 30 minute timer, though)

So let's get started:

Somehow (Workbook_Open event, etc.) the macro_timer procedure is run to start the inactivity clock (30 mins) running. When the OnTime timer is ended, the my_macro procedure opens the WakeUpCall form (if the time is greater than B1), which starts the 90 second form timer and also resets the 30 min OnTime timer by running macro_timer again. If the user clicks the button in WakeUpCall, the 90 second timer can get cancelled in addition to whatever else is going on with Sheets("INTRO"). Since the 30 minute timer has been re-started, the process repeats. If no button is clicked, the 90 second timer runs and calls the closeit procedure, which needs to stop the my_macro timer and do the rest of the stuff depending on Range("A2").

Code for WakeUpCall
VBA Code:
Dim CloseItInterval As Double

Private Sub CommandButton1_Click()
    Application.OnTime CloseItInterval, "LostArk.closeit", , False
    Application.ScreenUpdating = False
    Sheets("INTRO").Visible = True
    Sheets("INTRO").Select
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A4").Select
    Sheets("INTRO").Visible = xlSheetVeryHidden
    Application.ScreenUpdating = True
    Unload Me
End Sub

Sub UserForm_Initialize()
    CloseItInterval = Now + TimeValue("00:00:10")
    Application.OnTime CloseItInterval, "LostArk.closeit"
End Sub

LostArk module code:
VBA Code:
Dim interval As Double

Sub macro_timer()
    stop_macro
    interval = Now + TimeValue("00:00:30")
    Application.OnTime interval, "my_macro"
End Sub

Sub my_macro()
    Dim Today As Double
    Today = Now
    With Workbooks("PPP Play test")
        Workbooks("PPP Play test").Activate
        Sheets("INTRO").Visible = True
        Sheets("INTRO").Select
        If Today > Range("B1").Value Then
            WakeUpCall.Show ([False])
        End If
        Sheets(1).Select
        Sheets("INTRO").Visible = xlSheetVeryHidden
        macro_timer
    End With
End Sub

Sub stop_macro()
    On Error Resume Next
    Application.OnTime Earliesttime:=interval, Procedure:="my_macro", Schedule:=False
End Sub

Sub closeit()
    Unload WakeUpCall
    stop_macro
    With Workbooks("PPP Play test")
        Workbooks("PPP Play test").Activate
        If Sheets("INTRO").Range("A2").Value = "1" Then
            Application.ScreenUpdating = False
            Sheets("INTRO").Visible = True
            Sheets("INTRO").Select
            Range("A2").Select
            ActiveCell.FormulaR1C1 = ""
            Range("A4").Select
            Sheets("INTRO").Visible = xlSheetVeryHidden
            Application.ScreenUpdating = True
        Else
            ActiveWorkbook.Save
            ActiveWorkbook.Close
        End If
    End With
End Sub

I don't understand the interplay between the form button setting Range("A2") to be 1 and then not saving/closing the workbook if the user fails to click the button that forces the closeit procedure to run. However, that's not important to getting the timers to run.
thank you for the reply. I still seem to have the same issues as before.
Only with multiple worksheets open, if I close it before the userform pops up. then manually close the workbook, the workbook will reopen and reclose endlessly by the interval of the userform timer which is currently 10 seconds.
Also with multiple workbooks open, if the userform pops up and I dismiss it, then manually close it, the sheet will reopen and the closeit sub will pop an error with trying to hide the INTRO sheet. Now that error makes sense as the INTRO sheet is the only sheet visible. The problem that this details one details, is that if the sheet reopened, then all the sheets except intro should be visible:
VBA Code:
Private Sub Workbook_Open()
'Exit Sub 'This gets uncommented when I am working on the workbook to prevent autoclose.
Application.ScreenUpdating = False

Sheets("INTRO").Visible = True
Sheets("INTRO").Select
    Range("A2").Select
    ActiveCell.FormulaR1C1 = ""
    Range("A4").Select
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> ThisWorkbook.ActiveSheet.Name Then
ws.Visible = True
End If
Next ws
Sheets(1).Select
Sheets("INTRO").Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
Application.Run "LostArk.macro_timer"
End Sub

Now, as far as the A2 = 1, After looking at your comments, it does seem to be unnecessary. In fact, I don't know where I put an IF statement into the close it code. Ultimately, If I run the closeit code, it's because I want to close the workbook. I'll play with that and post an update, and that might solve that issue leaving only the endless reopening before the userform even pops up...
 

anichols

Board Regular
Joined
Mar 11, 2021
Messages
67
Office Version
  1. 365
Platform
  1. Windows
thank you for the reply. I still seem to have the same issues as before.
Only with multiple worksheets open, if I close it before the userform pops up. then manually close the workbook, the workbook will reopen and reclose endlessly by the interval of the userform timer which is currently 10 seconds.
Also with multiple workbooks open, if the userform pops up and I dismiss it, then manually close it, the sheet will reopen and the closeit sub will pop an error with trying to hide the INTRO sheet. Now that error makes sense as the INTRO sheet is the only sheet visible. The problem that this details one details, is that if the sheet reopened, then all the sheets except intro should be visible:
VBA Code:
Private Sub Workbook_Open()
'Exit Sub 'This gets uncommented when I am working on the workbook to prevent autoclose.
Application.ScreenUpdating = False

Sheets("INTRO").Visible = True
Sheets("INTRO").Select
    Range("A2").Select
    ActiveCell.FormulaR1C1 = ""
    Range("A4").Select
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> ThisWorkbook.ActiveSheet.Name Then
ws.Visible = True
End If
Next ws
Sheets(1).Select
Sheets("INTRO").Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
Application.Run "LostArk.macro_timer"
End Sub

Now, as far as the A2 = 1, After looking at your comments, it does seem to be unnecessary. In fact, I don't know where I put an IF statement into the close it code. Ultimately, If I run the closeit code, it's because I want to close the workbook. I'll play with that and post an update, and that might solve that issue leaving only the endless reopening before the userform even pops up...
Update: The A2 cell was meant to reset the formula on Intro. I just reset the code to
VBA Code:
Range("B3").Value = ""
. A1 is NOW, and B1 adds a set amount of time to the now. (for testing, 1 minute) I did remove the IF statement from closeit.

Doing that has caused more issues it seems.
VBA Code:
Sub closeit()
    Unload WakeUpCall
    stop_macro
    ActiveWorkbook.Save
    ActiveWorkbook.Close
End Sub
Now when I delay the userform and manually close it reopens, and closes a different workbook and keeps closing other workbooks every 10 seconds until it falls back into its own loop and reopens and closes. So I don't have an error pop up anymore but now it just don't stop.
 

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
I went even more basic and just have the functionality of the OnTimers. There is nothing related to the INFO sheet yet. We can add that later. This works for me with any number of workbooks open.

The macro_timer sets the my_macro timer at 5 seconds. my_macro is called at the end of the 5 seconds, so that timer is no longer running. my_macro creates a new WakeUpCall form that starts its own timer to run closeit. If CommandButton1 is clicked, the closeit timer is stopped, and macro_timer is run again to start the 5 second timer. If the X (top right close button) of the form is clicked, the form timer is stopped, but the macro_timer is not called, so the 5 second timer doesn't restart. We can change this behaviour later on (I'm not sure what you wanted to do if the form was closed without clicking on CommandButton1). If nothing is done with the form, closeit is called, which ends the form's OnTimer. At this point, there is no outstanding OnTimer running because the form hasn't restarted macro_timer, and it's timer ended by calling closeit. If the workbook is closed, stop_macro is called which cancels any OnTimer running (right now it doesn't automatically save in the BeforeClose event, but that can be added if needed).

Code for ThisWorkbook:
VBA Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    stop_macro
End Sub

Code for LostArk module:
VBA Code:
Dim interval As Double
Dim WakeUp As WakeUpCall

Public Sub macro_timer()
    stop_macro
    interval = Now + TimeValue("00:00:05")
    Application.OnTime interval, "my_macro"
End Sub

Sub my_macro()
    Set WakeUp = New WakeUpCall
    WakeUp.Show vbModeless
End Sub

Public Sub stop_macro()
    If Not WakeUp Is Nothing Then
        Unload WakeUp
        Set WakeUp = Nothing
    End If
    On Error Resume Next
    Application.OnTime EarliestTime:=interval, Procedure:="my_macro", Schedule:=False
End Sub

Public Sub closeit()
    ActiveWorkbook.Save
    ActiveWorkbook.Close
End Sub

Code for WakeUpCall form:
VBA Code:
Public CloseItInterval As Double

Private Sub CommandButton1_Click()
    StopFormOnTime
    macro_timer
    Unload Me
End Sub

Sub UserForm_Initialize()
    CloseItInterval = Now + TimeValue("00:00:10")
    Application.OnTime CloseItInterval, "LostArk.closeit"
End Sub

Private Sub UserForm_Terminate()
    StopFormOnTime
End Sub

Private Sub StopFormOnTime()
    On Error Resume Next
    Application.OnTime EarliestTime:=CloseItInterval, Procedure:="LostArk.closeit", Schedule:=False
End Sub

As I said above, this works for me to successfully bring up the "inactivity" form every 5 seconds. Clicking on CommandButton1 continuously re-brings up the form every 5 seconds. Doing nothing with the form saves and closes the workbook. Closing the workbook during the running of either OnTimer successfully stops both from running and closes the workbook. I am not experiencing any follow-up triggers.
 

anichols

Board Regular
Joined
Mar 11, 2021
Messages
67
Office Version
  1. 365
Platform
  1. Windows
Very Cool! I will Try that out today. Additionally, I managed to get everything to work without any follow-up triggers as well. It's a bit different than yours, but I'll post it and explain for anybody else that maybe be looking for this type of code: (Note: my peers suggested a new name for my program that would watch for inactivity, so the Program is now called "Hawkeye")

Open & Closing Code:
VBA Code:
Private Sub Workbook_Open()
'Installation Notes:
'Add these two subs to this workbook page, Import the userform and Module from the Hawkeye Folder in Automation, Open Hawkeye Workbook and copy INTRO tab onto other sheet, Update the file names in the SHIELD module.
'Also note the formula in B1 + the time in macro_timer creates a "range" with the minimum being the calculated time in B1 and the maximum being the the sum of b1 & macro_timer!
Application.ScreenUpdating = False
Sheets("INTRO").Visible = True
Sheets("INTRO").Select
    Range("A2").Select
    ActiveCell.FormulaR1C1 = ""
    Range("A4").Select
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> ThisWorkbook.ActiveSheet.Name Then
ws.Visible = True
End If
Next ws
Sheets(1).Select
Sheets("INTRO").Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
Application.Run "SHIELD.macro_timer"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
Sheets("INTRO").Visible = True
Sheets("INTRO").Select
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> ThisWorkbook.ActiveSheet.Name Then
ws.Visible = xlSheetVeryHidden
End If
Next ws
Application.Run "SHIELD.stop_macro"
Application.Run "SHIELD.stop_macro"
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub

This code makes sure that the only sheet visible is the Intro sheet which reminds users that macros must be enabled to use this workbook. If Macros are enabled to begin with, the Intro sheet will never be seen.
Module: SHIELD
VBA Code:
Public interval As Double
Sub macro_timer()
interval = Now + TimeValue("00:01:30") 'this time MUST be longer (probs 2x) than the formula adjustment in B1
Application.OnTime interval, "my_macro"
End Sub
Sub my_macro()
Dim Today
Today = Now
'---------------------Update Correct File Name Here:------------------------------------------
With Workbooks("HAWKEYE")
Workbooks("HAWKEYE").Activate
Sheets("INTRO").Visible = True
Sheets("INTRO").Select
If Today > Range("B1").Value Then
    WakeUpCall.Show ([False])
Else
Call macro_timer
End If
Sheets(1).Select
Sheets("INTRO").Visible = xlSheetVeryHidden
End With
End Sub
Sub stop_macro()
On Error Resume Next
Application.OnTime Earliesttime:=interval, Procedure:="my_macro", Schedule:=False
Application.OnTime Earliesttime:=CloseItInterval, Procedure:="closeit", Schedule:=False
End Sub
Sub closeit()
'---------------------Update Correct File Name Here:------------------------------------------
With Workbooks("HAWKEYE")
Workbooks("HAWKEYE").Activate
For Each Sheet In ThisWorkbook.Sheets
If Sheet.Visible = True Then i = i + 1
Next
If i = 1 Then
    stop_macro
    ActiveWorkbook.Save
    ActiveWorkbook.Close
End If
    If Sheets("INTRO").Range("A2").Value = "1" Then
    Application.ScreenUpdating = False
    Sheets("INTRO").Visible = True
    Sheets("INTRO").Select
    Range("A2").Select
    ActiveCell.FormulaR1C1 = ""
    Range("A4").Select
    Sheets("INTRO").Visible = xlSheetVeryHidden
    Application.ScreenUpdating = True
    Unload WakeUpCall
    Else
    stop_macro
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    End If
End With
End Sub

This will run a timer every xx minutes and then check it the current time is greater than the time set in B1 of our hidden INTRO tab. (That time should be set as less than the macro_timer time to create a time range)
This code is the userform that will close or extend the open time.
VBA Code:
Dim CloseItInterval As Double

Private Sub CommandButton1_Click()
    Application.OnTime CloseItInterval, "SHIELD.closeit", , False
    Application.ScreenUpdating = False
    Sheets("INTRO").Visible = True
    Sheets("INTRO").Select
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A4").Select
    Sheets("INTRO").Visible = xlSheetVeryHidden
    Application.ScreenUpdating = True
    macro_timer
    Unload Me
End Sub


Sub UserForm_Initialize()
    CloseItInterval = Now + TimeValue("00:00:10")
    Application.OnTime CloseItInterval, "SHIELD.closeit"
End Sub
 

Forum statistics

Threads
1,141,576
Messages
5,707,188
Members
421,497
Latest member
philipg

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