Forum Discussion
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
- JKPieterseSilver 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