MsgBox not working and Reopen option

[eli_m]

Hi All,

For some reason this code works great but the MsgBox command doesn't work anymore:

Option Explicit
Public DownTime As Double

Sub StartTimer()
    DownTime = Now + TimeSerial(0, 30, 0)
    Application.OnTime DownTime, "ShutDown"
End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime DownTime, "ShutDown", , False
 End Sub

Sub ShutDown()
    With ThisWorkbook
        Application.DisplayAlerts = False
        .Close Savechanges:=True
        Application.DisplayAlerts = True
    End With
        MsgBox "Timed out after 30 minutes - Your work has been saved and closed"
End Sub

Also is there a way to have a "Reopen" button so it reopens the Workbook it just closed as well as a "OK" button?

Thanks in advance!

 

[eli_m]

And to answer your other question. I have had problems saving to Cloud locations. I had to use the local file that syncs.

I share this document with other people - is there a way for it to save the file path of that computer and reopen that link but only for that person?
I don't want to open the file path of another user

 

[Skyybot]

I share this document with other people - is there a way for it to save the file path of that computer and reopen that link but only for that person?
I don't want to open the file path of another user

I've been working on this problem myself. I'm going to try and use String and Length formulas to fix the issue. Best I can tell you is to search the InterWebs for your specific Cloud Location Storage. I use OneDrive so that's what I'm working on.

 

[eli_m]

I've been working on this problem myself. I'm going to try and use String and Length formulas to fix the issue. Best I can tell you is to search the InterWebs for your specific Cloud Location Storage. I use OneDrive so that's what I'm working on.

Mine is actually on my personal onedrive and I share it to the others

 

[Skyybot]

Dude, I'm so dumb.

Option Explicit
Public DownTime As Double
Dim Ans as Variant
Sub StartTimer()
    DownTime = Now + TimeSerial(0, 30, 0)
    Application.OnTime DownTime, "ShutDown"
End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime DownTime, "ShutDown", , False
 End Sub
Sub ShutDown()
    With ThisWorkbook
        Application.DisplayAlerts = False
Ans = MsgBox("Would you like to continue working?", vbYesNo, "Done Working?")
           If Ans = vbNo Then
                     .Close Savechanges:=True
           End If
        
        Application.DisplayAlerts = True
    End With
       
End Sub

 

[eli_m]

Dude, I'm so dumb.

Option Explicit
Public DownTime As Double
Dim Ans as Variant
Sub StartTimer()
    DownTime = Now + TimeSerial(0, 30, 0)
    Application.OnTime DownTime, "ShutDown"
End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime DownTime, "ShutDown", , False
 End Sub
Sub ShutDown()
    With ThisWorkbook
        Application.DisplayAlerts = False
Ans = MsgBox("Would you like to continue working?", vbYesNo, "Done Working?")
           If Ans = vbNo Then
                     .Close Savechanges:=True
           End If
       
        Application.DisplayAlerts = True
    End With
      
End Sub

Now I think my problem is that I actually want it to close the document and then give the option to reopen or close.
The whole point was to make it so that no one leaves the Workbook open as I have had others leave it open on their laptops all day and cause sync issues. This doesn't close the document until the button is pressed right? If so that doesn't help my issue sadly

 

[Skyybot]

Now I think my problem is that I actually want it to close the document and then give the option to reopen or close.
The whole point was to make it so that no one leaves the Workbook open as I have had others leave it open on their laptops all day and cause sync issues. This doesn't close the document until the button is pressed right? If so that doesn't help my issue sadly

Ah...So we still have the same issue with saving to Coud locations. I will continue digging and keep you posted. Is it okay if I message you direct if I find a solution?

 

[Skyybot]

You also might try a SaveChanges without Close in the Shutdown Sub. Saving alone should work with Cloud files.

 

[eli_m]

Ah...So we still have the same issue with saving to Coud locations. I will continue digging and keep you posted. Is it okay if I message you direct if I find a solution?

Yes of course! I really appreciate your help. The code I marked as a solution - will that still close down the Workbook after the 30 minutes?

 

[eli_m]

Yes of course! I really appreciate your help. The code I marked as a solution - will that still close down the Workbook after the 30 minutes?

eg:

Option Explicit
Public DownTime As Double

Sub StartTimer()
    DownTime = Now + TimeSerial(0, 30, 0)
    Application.OnTime DownTime, "ShutDown"
End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime DownTime, "ShutDown", , False
 End Sub
Sub ShutDown()
    With ThisWorkbook
        Application.DisplayAlerts = False
        MsgBox "Timed out after 30 minutes - Your work has been saved and closed."
        .Close Savechanges:=True
        Application.DisplayAlerts = True
    End With
       
End Sub

Does it still run .Close Savechanges:=True even though the dialogue is on my screen?

 

[Skyybot]

eg:

Option Explicit
Public DownTime As Double

Sub StartTimer()
    DownTime = Now + TimeSerial(0, 30, 0)
    Application.OnTime DownTime, "ShutDown"
End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime DownTime, "ShutDown", , False
 End Sub
Sub ShutDown()
    With ThisWorkbook
        Application.DisplayAlerts = False
        MsgBox "Timed out after 30 minutes - Your work has been saved and closed."
        .Close Savechanges:=True
        Application.DisplayAlerts = True
    End With
      
End Sub

Does it still run .Close Savechanges:=True even though the dialogue is on my screen?

No. Any sort of Box requires user input to continue the code, so any code after that won't run until there is input. In order to save AND close a Workbook with code continuing, is to open a hidden Workbook that controls the code. I have a Macro Enable Template that always opens when I run Excel. But that hidden file will have to be created and saved for each user. It can be done programmatically as long as the document is Trusted.