MsgBox not working and Reopen option

eli_m

Board Regular
Joined
Jun 2, 2022
Messages
121
Office Version
  1. 365
Platform
  1. Windows
Hi All,

For some reason this code works great but the MsgBox command doesn't work anymore:
VBA Code:
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!
 
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
 
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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.
 
Upvote 0
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
 
Upvote 0
Dude, I'm so dumb.
VBA Code:
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
 
Upvote 0
Dude, I'm so dumb.
VBA Code:
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
 
Upvote 0
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?
 
Upvote 0
You also might try a SaveChanges without Close in the Shutdown Sub. Saving alone should work with Cloud files.
 
Upvote 0
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?
 
Upvote 0
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:
VBA Code:
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?
 
Upvote 0
eg:
VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

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
Back
Top