I created an Excel workbook, housed on a network drive, that is accessable by three people involved in entering data - myself and 2 co-workers. The workbook contains three (3) worksheets - database-which is very hidden, a support worksheet containing tables-used to lookup values on the userform-which is hidden, and a "home" worksheet - the first sheet one sees when the workbook is opened. The userform is launched from the home worksheet via a command button. Once the user has finished their entry, there is a "save" command button on the userform. The support worksheet can be viewed by also clicking a command button on the "home" worksheet. I've added a "timer" so that the worksheet will automatically close after 5 minutes of inactivity (based on the first person opening the workbook).
Sub SetTimer()
DownTime = Now + TimeValue("00:05:00")
Application.OnTime EarliestTime:=DownTime, _
Procedure:="ShutDown", Schedule:=True
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=DownTime, _
Procedure:="ShutDown", Schedule:=False
End Sub
Sub ShutDown()
Application.DisplayAlerts = False
With ThisWorkbook
.Saved = True
.Close
End With
End Sub
After the elapsed time, the support tab will be hidden and the file will close. After a lot of testing, and learning VBA along the way, everything works fine.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Worksheets("Home").Visible = True
Worksheets("Support").Visible = False
'must save, if not save, it is not in effect.
Application.DisplayAlerts = True
Call StopTimer
Me.Save
End Sub
My dilemma: When one person has the workbook open, and a second person opens the workbook, Excel lets the second person know the workbook is already open. Unfortunately, when the second instance of the workbook is opened, and the user will make an entry (instead of waiting until the file is closed), the user will "save as" and close the file - creating a duplicate file which requires manual merging of new data into the original file.
I have added a command button to the "home" worksheet, with a macro designed to exit but not save the file (for the situation described above). The problem is that, instead of using the Exit button, the second user will click the "X" on the worksheet to close the file, which again brings up the message to "save", "save as", or "cancel".
How can I (1) supress the "X" on the "home" worksheet, forcing a second user to use the Exit button without saving, or saving as, the file, and (2) allow the timer to continue to run so that the file will automatically close after the elapsed period of inactivity when the first person opened the file? Searched numerous forums and sites, and found code to suppress the "X" on the "home" worksheet, but the timer does not continue to run.
I'm not certain if both of the above can be accomplished, but would welcome any assistance, direction as to what I'm missing.