Help, need advice

[Mikeyhend]

Dear friends,

I have a problem with macro's I use in a network environment. If too many people are linking to the same file at the same time, the workbook is getting damaged, leaving only text, and one time I couldn't even open it again with excel.
The workbooks aren't very big; they are used as registration lists for trainings, with macro's that select the first empty cell if the lists are very long, that gives an inputbox if someone forgets to fill in some data and a macro that locks the cells with data in, so people can't overwrite or delete registrations.
I disable 'tools' from the menubar so people can't unlock the sheets or look for the password themselves.

If the book is opened readonly, I give a messagebox and make it close immediately, without saving.

Usually the employees receive an e-mail with information about the training and a link to the file(s). Registrations are between 20 and 700 per training,
depending on the target group. The files for little groups, with registrations spread over a longer period of time, cause no problems.

What could be the reason the books are getting damaged if the target group is very large?

Best regards,
Mike

[dok112]

Mike,

Is it happening when they are trying access the file? or adding to it? Also, are multiple people trying to access the file at once?

[Mikeyhend]

I think it happens when they access the files or at closing time, but I'm not sure about that. Sometimes a mail is sent to 700 employees at once, so it is possible 30, 40? people try to access the files at the same time. I noticed it went wrong at these moments and between 8.00 and 9.00 o'clock, when most of them read their mails and then register if received an e-mail. Maybe the number of copies opened in excel is limited? Or could it be due to the links to the files? I don't know.
On the moment I have a file for a training for 20 people at once, for a limited target groupe, free to participate when it fits the best, with multiple sheets, each for another date. This file is now accessible for 3 months and I have never had problems with it.
Since last week I have a new training, mandatory for all 700 employees, registrations spread over 7 files, 7 dates, 100 per file. After 4 days one the files crashed? At that time it should have been after 'rush hour'. I followed it up every morning and I noticed if I opened one of the files 'readonly' and closed it again, the hourglass (mouse pointer) stayed on untill I closed the central file linking to the registration lists. ( We have one central survey that contains all the titles of the trainings running for the next 2 months, with links to the registration lists). This isn't right and different with the previous lists and I still use almost the same code. I really don't know.
Maybe I'm forgetting something important in my code?

[frederic]

I would say that if a file is already opened , i would restrict access to another user. And give them the message to try later.
Because i'm seriously convienced that an excel file is not the ultimate tool to put on the local network. I would do this as an ultimate thing , because such files are extremely unsafe to create an 'error-free' inscription system.

Good luck.

Best regards ,

Fréderic (B)