none
AutoSave and Force Enable Prompt in Excel RRS feed

  • Question

  • Hi All,

    I have been redirected here from the Microsoft Forum as they say this is where the gurus do their magic..

    If you could please help...

    Very simply I am trying to set up a macro in excel to have a prompt to enable macros and then after a period of inactivity have the workbook auto save and close.

    I have managed to get it to do get it to work as far as it will come up with the msg upon opening and auto closing after inactivity but an amends are never saved upon auto close.. Can someone help please..

    I have the below coding in "this workbook"

    Const WelcomePage = "Macros"
     
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
         'Turn off events to prevent unwanted loops
        Application.EnableEvents = False
        
         'Evaluate if workbook is saved and emulate default propmts
        With ThisWorkbook
            If Not .Saved Then
                Select Case MsgBox("Do you want to save the changes you made to '" & .Name & "'?", _
                    vbYesNoCancel + vbExclamation)
                Case Is = vbYes
                     'Call customized save routine
                    Call CustomSave
                Case Is = vbNo
                     'Do not save
                Case Is = vbCancel
                     'Set up procedure to cancel close
                    Cancel = True
                End Select
            End If
            
             'If Cancel was clicked, turn events back on and cancel close,
             'otherwise close the workbook without saving further changes
            If Not Cancel = True Then
                .Saved = True
                Application.EnableEvents = True
                .Close SaveChanges:=False
            Else
                Application.EnableEvents = True
            End If
        End With
    End Sub
     
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
         'Turn off events to prevent unwanted loops
        Application.EnableEvents = False
        
         'Call customized save routine and set workbook's saved property to true
         '(To cancel regular saving)
        Call CustomSave(SaveAsUI)
        Cancel = True
        
         'Turn events back on an set saved property to true
        Application.EnableEvents = True
        ThisWorkbook.Saved = True
    End Sub
     
    Private Sub Workbook_Open()
         'Unhide all worksheets
        Application.ScreenUpdating = False
        Call ShowAllSheets
        Application.ScreenUpdating = True
    End Sub
     
    Private Sub CustomSave(Optional SaveAs As Boolean)
        Dim ws As Worksheet, aWs As Worksheet, newFname As String
         'Turn off screen flashing
        Application.ScreenUpdating = False
        
         'Record active worksheet
        Set aWs = ActiveSheet
        
         'Hide all sheets
        Call HideAllSheets
        
         'Save workbook directly or prompt for saveas filename
        If SaveAs = True Then
            newFname = Application.GetSaveAsFilename( _
            fileFilter:="Excel Files (*.xls), *.xls")
            If Not newFname = "False" Then ThisWorkbook.SaveAs newFname
        Else
            ThisWorkbook.Save
        End If
        
         'Restore file to where user was
        Call ShowAllSheets
        aWs.Activate
        
         'Restore screen updates
        Application.ScreenUpdating = True
    End Sub
     
    Private Sub HideAllSheets()
         'Hide all worksheets except the macro welcome page
        Dim ws As Worksheet
        
        Worksheets(WelcomePage).Visible = xlSheetVisible
        
        For Each ws In ThisWorkbook.Worksheets
            If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVeryHidden
        Next ws
        
        Worksheets(WelcomePage).Activate
    End Sub
     
    Private Sub ShowAllSheets()
         'Show all worksheets except the macro welcome page
        
        Dim ws As Worksheet
        
        For Each ws In ThisWorkbook.Worksheets
            If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
        Next ws
        
        Worksheets(WelcomePage).Visible = xlSheetVeryHidden
    End Sub
     Private Sub Workbook1_Open()
     Reset
     End Sub
     Private Sub Workbook_SheetActivate(ByVal Sh As Object)
     Reset
     End Sub
     Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
     Reset
     End Sub

    and the below is in "module"

    Sub Reset()
     Static SchedSave
         If SchedSave <> 0 Then
         Application.OnTime SchedSave, "SaveWork", , False
         End If
         SchedSave = Now + TimeValue("00:00:30") ' 30 seconds
         Application.OnTime SchedSave, "SaveWork", , True
     End Sub
     Sub SaveWork()
     ThisWorkbook.Save
     ThisWorkbook.Close
     End Sub

    Thanks again

    Monday, November 28, 2016 8:56 AM

Answers

  • Great - this exactly what I already had, but I need the document to automatically save changes and

    So hard?

    Change
      ThisWorkbook.Close False
    to
      ThisWorkbook.Close True
    • Marked as answer by CentralPha Thursday, December 15, 2016 10:10 AM
    Monday, December 12, 2016 5:05 PM

All replies

  • Very simply I am trying to set up a macro in excel to have a prompt to enable macros and then after a period of inactivity have the workbook auto save and close.

    a) The prompt to enable macros is an Excel built in feature and is displayed based on your security settings.

    b) Replace the code in the code module ThisWorkbook with the code below.

    Andreas.

    Option Explicit
    
    Const AutoCloseDelay  As Date = "00:00:05"
    
    Sub MyClose()
      'Save and close this workbook without demand
      Application.DisplayAlerts = False
      ThisWorkbook.Close True
    End Sub
    
    Private Sub SetupSchedule()
      'Setup or update our scheduled macro
      Schedule ThisWorkbook.CodeName & ".MyClose", Now + AutoCloseDelay
    End Sub
    
    Private Sub Schedule(ByVal Procedure As String, _
        Optional ByVal EarliestTime As Date, Optional ByVal LatestTime As Variant, _
        Optional ByVal Schedule As Boolean = True)
      'Manages scheduled macros
      Static Scheduler As Object
      Dim Items, Keys, i As Integer
      If Scheduler Is Nothing Then Set Scheduler = CreateObject("Scripting.Dictionary")
      If Procedure = "" Then
        Keys = Scheduler.Keys
        Items = Scheduler.Items
        For i = 0 To UBound(Items)
          Application.OnTime Items(i), Keys(i), Schedule:=False
        Next
        Scheduler.RemoveAll
        Exit Sub
      Else
        If Scheduler.Exists(Procedure) Then
          On Error Resume Next
          Application.OnTime Scheduler.Item(Procedure), Procedure, Schedule:=False
          On Error GoTo 0
          Scheduler.Remove Procedure
        End If
      End If
      If Not Schedule Then Exit Sub
      Application.OnTime EarliestTime, Procedure, LatestTime
      Scheduler.Add Procedure, EarliestTime
    End Sub
    
    'The time of execution is shifted in each of these event handlers:
    
    Private Sub Workbook_Open()
      SetupSchedule
    End Sub
    
    Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
      SetupSchedule
    End Sub
    
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
      SetupSchedule
    End Sub
    
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
      SetupSchedule
    End Sub

    Monday, November 28, 2016 11:20 AM
  • Hello,

    Thank you for your reply, however, this is just a auto save and close without the save prompt before closing. I also require the prompt page so that a user will actually see a message that they have to click on the enable macro first without being able to see the rest of the pages.. I assume that if they don't accept the macro then this code will not work either way which will defeat my objective.

    Thank again I await your response.

    Sorry after reading my posts maybe its not very clear what I require or have already...

    The code I originally posted basically when you open the excel document one of the pages title Prompt only shows and you can write a message on the sheet whatever you choose - I chose if you wish to view the rest of the document please click on the enable macro first. Once you click it the pages disappears and the rest of the workbook is displayed. as long as you are working on the file it will not auto close. However after the period of inactivity which I chose the workbook would attempt to close but the do you want to save your file prompt appears and this is what I don't want as if a user has gone to lunch or decided to leave it open then no one else can use it. I do have another macro that will do the same thing it will show the prompt upon opening and it will close without the message but it doesn't save the work.

    Please let me know.

    Thank you again

    • Edited by CentralPha Tuesday, November 29, 2016 11:13 AM further explaination
    Tuesday, November 29, 2016 10:47 AM
  • this is just a auto save and close without the save prompt before closing.

    When the time is up and the file should be closed, but the user is not on the PC, then the file is never closed.

    That makes no sense.

    Andreas.

    Tuesday, November 29, 2016 11:12 AM
  • Hi Andreas,

    Sorry, I have a file that multiple users work on but some users like to stay in the file without actually using it so no one else can go in.. so I had a macro that basically when you open the file and click enable macro the macro will once the the period of inactivity has reached the file would auto save and auto close without a prompt message appearing.. this worked great providing the users clicked enable macro but if they didn't then it would not ever close unless they closed the file personally..

    so I then found another macro which when set up you rename one of the sheets prompt and type any message of your choice on there. When you opened this file the only sheet/tab you would see was the prompt message but once you clicked on enable macro that sheet/tab would disappear and the rest of the sheets/tabs would appear.. this worked great but this did not provide a solution to if the user stayed in the file all day as it did not have the auto save and auto close macro included. So I set about trying to combine one that would - every time you open the file it would show you the prompt page instructing you to click enable macro and have an auto save and auto close without a prompt message the closest I have is the macro I posted original basically does the following:

    When a users opens the file the prompt sheet appears - no other sheets are visible until you click on enable macro once you click it that sheet goes/disappears and the rest appear and after a period of inactivity the file would auto save and try to auto close but what happens is the box prompt appears saying "do you want to save yes no cancel" which means if the user clicks no or cancel or is on lunch then the file will remain unusable to everyone else. so my questions is is there a way to have a macro doing the auto save and auto close without the message appearing asking you to save yes no cancel whilst still having the prompt page when you first open the file and the user can not continue to see the rest of the sheets/tab until they click enable macro..

    Hope this makes sense now.

    Tuesday, November 29, 2016 12:50 PM
  • When a users opens the file the prompt sheet appears - no other sheets are visible until you click on enable macro once you click it that sheet goes/disappears and the rest appear and after a period of inactivity the file would auto save and try to auto close

    Much too complicated, add this line as first line in Workbook_Open:

    If MsgBox("Enable AutoClose", vbOKCancel) = vbCancel Then ThisWorkbook.Close
    If the user click Ok, he can work with the file etc., otherwise the file closes immediately.

    You can customize the prompt of the MsgBox, insert vbCrLf into the prompt for line breaks, e.g.:

    MsgBox "Hello" & vbCrLf & "World"

    If you want to have more text or whatever create a Userform...

    Andreas.

    Tuesday, November 29, 2016 2:34 PM
  • Hello Andreas,

    Sorry I don't understand where I am to paste...

    Where in the macro and yours or my original do I put the below?

    If MsgBox("Enable AutoClose", vbOKCancel) = vbCancel Then ThisWorkbook.Close

    And also what would I need to do for the message to say:

    Please Enable Macro To Continue

    ----------------------------------

    Thank you so much for your help Andreas.


    • Edited by CentralPha Tuesday, November 29, 2016 3:58 PM
    Tuesday, November 29, 2016 3:57 PM
  • Hi Andreas,

    I have just tried it, I added the BOLD text below to the below macro section and what happens is the box prompt will only ever appear after the users clicks on Enable Macro so if the users never clicks on Enable Macro they can actually carry on working and the Auto Save and Auto Close never happens because they never see your prompt box.. hope this makes sense.

    --------------------------

    Private Sub Worbook1_Open()

    IfMsgBox("Enable AutoClose",vbOKCancel) =vbCancel ThenThisWorkbook.Close

    Reset

    End Sub

    -------------------------

    My original macro does everything other than it does not save the work,  I have another macro which will have a prompt page the same as the original macro but it will only ever close when you click on the prompt box that appears saying save yes no cancel which I am trying to stop appearing so that it does it automaticlaly.

    I think we are near to the solution.... thanks Andreas



    • Edited by CentralPha Tuesday, November 29, 2016 4:46 PM
    Tuesday, November 29, 2016 4:17 PM
  • Private Sub Worbook1_Open()

    IfMsgBox("Enable AutoClose",vbOKCancel) =vbCancel ThenThisWorkbook.Close

    Reset

    End Sub


    That makes absolutely no sense.

    Suggestion: Use the code above as is and in addition use the BeforeSave event to hide all sheets, except your Welcome sheet. Within that sheet you can place a button to unhide the other sheets.

    Andreas.

    Wednesday, November 30, 2016 5:03 PM
  • Hi,

    Do you resolve your issue? We would appreciate if you could share the solution here.

    If no, I am not sure what is your "Enable Macros" prompts. Or that is a messgebox you want to create now. I think there is no need to create it.

    Please visit Always enable macros in a workbook: If you know the macro is from someone you trust, click Enable Content. The next time you open the file, you won’t see the message bar and the macros will be enabled.

    The prompt in message bar depends on the macro settings. And we are unable to force users to Enable Content if they have a high security settings. If it is a high level security settings, all the macro would not execute and the file would be still opened even they leave.

    If users have low macro settings like Enable All Macros. I think the code above has met your requirement.

    Regards,

    Celeste



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 5, 2016 5:53 AM
    Moderator
  • Good Morning Andreas,

    I have been away for a couple of weeks sorry..

    I don't understand what you mean please can you insert what I need to do what I require please?

    thank you soo much :-)

    Monday, December 12, 2016 11:22 AM
  • Hello,

    sorry this is a not as straight forward as it should be..

    I require a workbook that when opened you are only shown one sheet which you can write a comment in it example PLEASE CLICK ON ENABLE MACRO when the users does this they then are given access to the other sheets.. this is the hide and unhide macro. along with this macro I require the workbook to close after a set period of inactivity by the user again this macro works but the issue I have is that the macro prompts a box saying to close which defeats the object as the user could not be at there desk at that time..

    So to summarise I require a macro to do the following:

    When a user opens a workbook they only see 1 page which has an instruction to enable macro and unless they click on there enable macro then they cant see the rest of the workbook.

    then once they click it the prompt page disappears and they are shown the rest of the workbook.

    lastly whilst they are using the workbook then they will continue to work in it but as soon as they have left it idle for a given time the work book will automatically save and close its self without prompting the user to click do you want to save.

    So far I have a macro that will give a prompt then unhide the sheets but will not auto close it will only get to the point of asking to save

    I also have another macro that will do the same but will close but not save.

    so I am close but not there.

    Thanks

    Monday, December 12, 2016 11:32 AM
  • I don't understand what you mean please can you insert what I need to do what I require please?

    Here is a sample file
    https://dl.dropboxusercontent.com/u/35239054/Welcome%20Screen.xlsm

    Andreas.

    Monday, December 12, 2016 4:28 PM
  • Hi Andreas,

    Great - this exactly what I already had, but I need the document to automatically save changes and then close, is this something you can do? At present you document does not save it just closes but if a user forgets to save anything and they go to lunch or a meeting then they lose everything so hence why I need it to save itself before it closes..

    Thanks

    Monday, December 12, 2016 4:54 PM
  • Great - this exactly what I already had, but I need the document to automatically save changes and

    So hard?

    Change
      ThisWorkbook.Close False
    to
      ThisWorkbook.Close True
    • Marked as answer by CentralPha Thursday, December 15, 2016 10:10 AM
    Monday, December 12, 2016 5:05 PM
  • Hi Andreas

    whilst doing a test on this I have found that this will only work providing that only 1 excel workbook is open, if I open this file and then I open another file this will not auto save and close?


    Thank you

    Thursday, December 15, 2016 12:27 PM