# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  Need Truth on Shared workbook & VB

## dimitrz

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.
```

----------


## dimitrz

No response yet :-(

----------


## shg

I'd help if I could, but I don''t use shared workbooks. In the meanwhile, though, please read the Forum Rules about Code Tags.

----------


## dimitrz

Dont know how to put proper code for this question and no edit button to try .... anyways apologise for not keeping it with forum rules

Hope this is better but how do I put that

----------


## royUK

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!

----------


## Tarball

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.

----------


## dimitrz

Thanks all for the contribution

----------


## dimitrz

Thanks all for the contribution

I was contemplating on using a FORM in the first sheet and have the data go into second sheet for user

Then I plan to have a link so that the data is taken out of that sheet lets say every half an hour and be updated on to a diffrent workbook altogather - I presume that would prevent any accidental changes in the overall data aswell

What do you guys think?

Roy yes Understood what you meant by code tags would be careful in future

----------


## dimitrz

> 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 !




The site's giving me an error  when I try to click on my account to change that.

----------


## Tarball

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.

----------


## dimitrz

I understand Tarball....to be quite honest - excel I have little bit of clue - MSAcess I may have to find someone to create the program that I need - and My company is not going to fund me on that since its my personal initative to help my team members make their life easier. and another factor that I need to look at is -  I am poor in excel and my team memebers are lost when using excel - If I ask them to do something on MS acess they will freakout :-)

----------

