Help, need advice

[Mikeyhend]

Dear friends,

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

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

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

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

Best regards,
Mik

 

[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)