Save and Close Unless Any Key Hit

rockyw

Well-known Member
Joined
Dec 26, 2010
Messages
1,191
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


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

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
How about a message box
Code:
      Selection.ClearComments
      Application.CalculateFull
      ActiveWorkbook.Save
      If MsgBox("Do you want to keep the file open", vbYesNo) = vbNo Then
         ActiveWorkbook.Close
      End If
 

rockyw

Well-known Member
Joined
Dec 26, 2010
Messages
1,191
Hey that will work, can a timer be used if no selection in 5 seconds it will close? Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 26, 2010
Messages
1,191

ADVERTISEMENT

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

Well-known Member
Joined
Dec 26, 2010
Messages
1,191
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,975
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

No
 

rockyw

Well-known Member
Joined
Dec 26, 2010
Messages
1,191
I keep seeing stuff about being able to do this with 2 macros? Is that possible?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,975
Office Version
  1. 365
Platform
  1. Windows
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
Code:
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
Code:
[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
 
Last edited:

rockyw

Well-known Member
Joined
Dec 26, 2010
Messages
1,191
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,963
Members
413,954
Latest member
mrsandy

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