Save and Close Unless Any Key Hit

[rockyw]

I'm using this code right now but would like to have an additional feature. Can this save and close unless the user hits a key or command button. That way when it opens I can be kept open if someone wants to view it longer. I will have this open, save and close once a day to keep it updated. Thanks

Private Sub Workbook_Open()
Dim R As Range
Dim Sht As Worksheet
Dim Cel As Range

    Set Sht = Sheets("Data")
    
    Set R = Sht.Range("B4:B27,K4:K27,T4:T27")
    
    For Each Cel In R
        If Cel.Value < 0 Then
            If Cel.Offset(0, 8).Value = "" Then Cel.Offset(0, 8) = Date
        ElseIf Cel.Value >= 0 Then
            Cel.Offset(0, 8).ClearContents
        End If
    Next Cel

    Set R = Sht.Range("T4:T27")
    For Each Cel In R
    If Cel.Value = 0 Then Cel.Offset(0, 9) = Date
    Next Cel
    Set R = Sht.Range("C32:C37")
    For Each Cel In R
    If Cel.Value = 0 Then Cel.Offset(0, 2) = Date
  Next Cel
  
      Selection.ClearComments
      Application.CalculateFull
      ActiveWorkbook.Save
End Sub

 

[Fluff]

How about a message box

      Selection.ClearComments
      Application.CalculateFull
      ActiveWorkbook.Save
      If MsgBox("Do you want to keep the file open", vbYesNo) = vbNo Then
         ActiveWorkbook.Close
      End If

 

[rockyw]

Hey that will work, can a timer be used if no selection in 5 seconds it will close? Thanks

 

[Fluff]

That I don't know, never having used timers, but I suspect not as the code will stop until you click one of the buttons on the message box.

 

[rockyw]

Maybe someone can help with this. I'm searching for a code but no luck yet. There has to be a way to give an option to keep it open AND if no repose it will close automatically. Thanks

 

[rockyw]

Selection.ClearComments
Application.CalculateFull
ActiveWorkbook.Save
If MsgBox("Do you want to keep the file open", vbYesNo) = vbNo Then
ActiveWorkbook.Close
End If

Is there no way to have this continue if no response in 5 seconds? Thanks

 

[Yongle]

No

 

[rockyw]

I keep seeing stuff about being able to do this with 2 macros? Is that possible?

 

[Yongle]

MESSAGE BOX
The reason that you cannot ask the user via message box is that ...
... a message box halts the code (period!) until a response is received from the user

ALTERNATIVE
Try something like this to allow the user some time to look without saving (refine it to suit yourself)
- workbook_open calls StartTimer thus delaying close by 15 seconds
- if user runs SetCloseBookToFalse then it prevents workbook from closing (attach a shortcut or function key to SetCloseBookToFalse)
- before_close macro resets the boolean variable to allow the file to close next time
- StartTimer is called again delaying close by a further 15 seconds
- user can delay again by running SetCloseBookToFalse
- otherwise file auto-closes next time

Please post your final version (if you decide to pursue), so that others can benefit - thanks

In ThisWorkbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If CloseBook = False Then
        Cancel = True
        CloseBook = True
        Call StartTimer                'delays by amount of timer
    Else
        ThisWorkbook.Saved = True
    End If
End Sub

Private Sub Workbook_Open()
[I] 'do whatever is required[/I]
        Sheets(1).Range("A1") = Now()  [I][COLOR=#006400] 'this is something forcing the workbook to update whilst I was testing[/COLOR][/I]
[I]'delay close[/I]
    CloseBook = True
    ThisWorkbook.Save
    Call StartTimer
End Sub

In Standard Module

[I]'declare variable ABOVE all procedures[/I]
Public CloseBook As Boolean

Sub StartTimer()
    Application.OnTime earliesttime:=Now + TimeSerial(0, 0, [COLOR=#ff0000]15[/COLOR]), procedure:="CloseFile", schedule:=True
End Sub

Sub CloseFile()
    ThisWorkbook.Close False
End Sub

Sub SetCloseBookToFalse()   [COLOR=#006400] 'attach this to a shortcut or function key so that the user can run it easily[/COLOR]
    CloseBook = False
End Sub

 

[rockyw]

Thanks for the advice but I ended up going a different direction. Every time the workbook opens and closes I he a macro to save a copy and the people that use it can look at the cop. It woks a little smoother. Thanks