Need Truth on Shared workbook & VB

Can someone clarify these doubts that I have?

Brief History

[ I have two Shared Workbook accessed by multiple users. Both workbook has multiple users adding around 100 rows everyday and each book has 3 sheets - one for Request tracker- one for Call Tracking (which has the VB code) and 3d sheet has name range for the drop down menus ]

1) Does a shared workbook become unstable if it has macro /VB in it?
I have two separate shared workbooks that are accessed by multiple users. After I added the macro one of these workbooks crashed and I got a file corruption error. I recreated that workbook and for almost 1 week it’s holding without issues.

2) I am using macro to make the trackers a bit more user friendly for my hardworking team , Some people say using macro is not advisable because over a period of time all macros become Virus and the file gets corrupted- Is there any truth to this ? I don’t want my important files to become unusable.
3) Is there a limit on how many macro/ VB scripts I can have in a workbook/sheet without making it unstable?
4) On an average how many rows of information can such a workbook take without out becoming unstable (eg 1000 or 2000 rows)
5) I am planning to combine both workbook into one – which would mean the users accessing the book would be double around 10 and the entries would also be double close to 500 rows of information everyday. On both sheets. Plus I plan to have more macros to automate most fields to ease the load on my team - Is that ok or a bad idea? (I would have a auto backup enabled )
6) Finally for the same workbook I am planning to have a more friendly FORM template to further ease the load on my team – can a form template take such a load [ I plan to have the form in Sheet I and the data inputed from the FORM would go to sheet 2 & 3 – would there be any issues with that ? There would be few automation macros like static time & date.

Coming to Backup workbook option during save process
1) Can I password protect a backup workbook – would the backup work in such a case? I don’t want someone to access the backup workbook accidentally and delete or input any data there (remember multiple users)
2) Does a Backup work book needs to be in the same folder as the original workbook - can’t I save the backup WB in a different folder in the same drive?

Would appreciate if someone can clarify my doubts on these points.




This is the macro I am using on sheet 2 of both workbook currently

Please Login or Register  to view this content.

You need to use Code Tags when posting code on the Forum, read the Rules to be sure you don't break them.

I have added Tags for you this time.

Excel is not really meant to be used by multiple users adding data like this. It should make no difference in the number of macros in the shared workbook. I suspect the crash was probably the result of too many users attempting to use the workbook.

You may also think it amusing to put your location as Earth, but we ask fior this information so that we can get an idea where our user's live and whether English is their first language!

You are working in a shared environment with what sounds like the potential for multiple simultaneous updates to a shared spreadsheet. This in and of itself has the potential for data loss.

I have not ever heard of the story that says all macros will eventually become unstable or will turn to virus! To me that simply will not happen. Now, if you do not have good programming practice then the macros you have written (and subsequent updates) will become very hard to maintain and will likely become full of "band aid" solutions.

With this sort of environment I think you are better off to use MS-Access. You will then still be able to report using MS-Excel directly from MS-Access.

Honestly in the time it will take you to develop a form, links and moving the data to another spreadsheet altogether you can have a fully functional database application. You could even split the database so that the tables are on a shared drive and your interface is on each users desktop. Doing this you avoid concurrent user contention. At worst a user is notified that the current record they are attempting to edit is locked by another user. You can do all of this using the MS-Access wizards.

Dont get me wrong, I am not telling you to not use MS-Excel but rather pointing out that there are other tools that should be able to do the job more efficiently.