Shared Workbook - Occasional Save Error

[dlg77]

Hello All,

I created a workbook that was meant to be used as a prototype for a program that would be developed in time. The customer liked it so much they want to keep using it and forego paying for development using an actual programming language. Essentially the spreadsheet uses a lot of conditional formatting to create a dashboard for keeping track of activities within a building. It works great for their needs and they like the simplicity of the interface. But adding an additional location (terminal with workbook open) is presenting me a challenge I haven't been able to overcome. Here's my issue: I thought the shared workbook feature would work until I realized how it works. So the changes at location one don't get updated at location two until the workbook gets saved. So I created a macro to save the workbook every minute. This works but about once a day one location generates one of two 1004 run time errors (either file is in use or file cannot be found). I'd like to "catch" when this happens, wait a few seconds, and restart the macro. Or, check if the file is available for saving prior to the macro running. What would be the best way to address my issue?

Here's my macro code:

Please Login or Register  to view this content.

[davesexcel]

Is this an actual "Shared Workbook" or a workbook that is shared?

[dlg77]

It's an actual Shared Workbook. After creating it I configured it for sharing. The workbook is stored on a network share and is opened in Excel at each location. On opening I have a macro run that calls the Save_Workbook function in my first post. It's code is:

Private Sub Workbook_Open()

Application.OnTime Now + TimeValue("00:01:00"), "Save_Workbook"

End Sub

[davesexcel]

VBA does not work very well with shared workbooks.