Forum Discussion

ticartertekni's avatar
ticartertekni
Copper Contributor
Jul 24, 2025

Password to modify pop-up after VBA timeout

Hi!

I have a spreadsheet that uses the Password to modify option from the Save As>Tool>General Options menu to allow the general production folk to access the file in Read-Only and the Engineering team to be able to make edits after entering the password. The file is on a shared network drive and to prevent someone from opening it and leaving it open thus locking everyone else out (multi-shift operations), there is also VBA that will close the file after an hour has passed. 

This issue I am running into is that the Password to modify pop-up will appear after the workbook has timed out. How do I stop this from happening?

Here is my code with some naming changes for anonymity: 

Module:

Dim CloseTime As Date

Sub TimeSetting()
      '60 min from idle start. Change as needed.
    CloseTime = Now + TimeValue("01:00:00")

    On Error Resume Next

    Application.OnTime EarliestTime:=CloseTime, _
      Procedure:="CloseDownFile", Schedule:=True

 End Sub

Sub TimeStop()

    On Error Resume Next
        Application.OnTime EarliestTime:=CloseTime, _
      Procedure:="CloseDownFile", Schedule:=False

 End Sub

Sub CloseDownFile()

    ' Closes the file WITHOUT saving.
    Workbooks("WorkbookName.xlsm").Close Savechanges:=False

End Sub

ThisWorkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'Protect worksheets on close
    Dim xsheet As Worksheet
    Dim xpsw As String
    xpsw = "Password"
    For Each xsheet In Worksheets
        xsheet.Protect xpsw
    Next

    Worksheets("Database").Visible = False
    Worksheets("Revisions").Visible = False
   
    If ActiveWorkbook.ReadOnly = True Then
    ' Closes the file WITHOUT asking to save.
    Workbooks("WorkbookName.xlsm").Close Savechanges:=False
    Else
    End If
    
    Call TimeStop
    
End Sub

Private Sub Workbook_Open()
'This event will execute immediately after the workbook is opened
    ' Display the UserForm TimeoutWarning
    TimeoutWarning.Show
    'TimeSetting resets the idle timer. See "CloseIdle" module.
    Call TimeSetting

    'Unprotect all sheets if password to modify is entered
    Dim xsheet As Worksheet
    Dim xpsw As String
    xpsw = "Password"
    If ActiveWorkbook.ReadOnly = False Then
    Worksheets("Database").Visible = True
    Worksheets("Revisions").Visible = True
        
    For Each xsheet In Worksheets
     sheet.Unprotect xpsw
    Next
    
    Else
    End If
    
End Sub
 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'This event will execute each time the contents of a cell are modified

    Call TimeStop
    Call TimeSetting
   
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'This event will execute each time the selection changes on a calculation worksheet
   
    Call TimeStop
    Call TimeSetting

End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'This event will execute each time the user switches from one worksheet to another
   
    Call TimeStop
    Call TimeSetting
   
End Sub

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
'This event will execute immediately after a save command is completed

    Call TimeStop
    Call TimeSetting

End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
'This event will execute each time a worksheet's data is calculated or recalculated

    Call TimeStop
    Call TimeSetting

End Sub

2 Replies

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor

    Make sure the call to TimeStop is made BEFORE you close the workbook.

  • How about trying to temporarily switch to another workbook for another workbook take focus right before the close:

     

    Sub CloseDownFile()
        Application.ScreenUpdating = False
        Workbooks.Add 'Creates a temporary new workbook
        Workbooks("WorkbookName.xlsm").Close SaveChanges:=False
        Application.ScreenUpdating = True
    End Sub

     

Resources