Updating Shared Workbooks

[shaggi]

I have set up a series of shared workbooks for more than one person to input details into at the same time. However to try to cut down conflicts etc, I would like them to update pretty much as soon as data is input. I am using Excel 97, but cannot find a way in excel or VB to get the update time less than the mandatory 5 minutes,any help would be much appreciated......

 

[calculus]

This is an interesting issue. I hope you're not talking about more than 2 or 3 people or this will get very tricky.

I use Excel 2000, hopefully this is adaptable to 97.

I would suggest saving the file as read only with VBA script in it. The change event should say get_reservation, save, release_reservation.

The problem is, if another person already has a copy of the file open, their copy will not be updated until they close and reopen.

I am not aware of a mandatory 5 minutes in 2000.

Good luck

 

[shaggi]

Thanks for your reply Calculas
97 has a preset minimum of 5 mins, what you are saying sounds interesting, but as a reletive newbie, could you give me a few lines of code or something?
Many thanks again
Adrian (Shaggi)

 

[calculus]

Well, my memory fails me a bit. That was Lotus I had done that in before. Here's the way it would look in Excel:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
ActiveWorkbook.ReadOnly = False
ActiveWorkbook.Save
ActiveWorkbook.ReadOnly = True
End Sub

This sub does fire on a change to any sheet except a chart (acording to the help files). But the ReadOnly property will not work in this instance. You may be able to tinker around with this and make it work if you think it will solve your needs.