What is best practice for developing an Excel VBA solution using multiple workbooks

Hello

I am attempting to creating an Excel VBA solution to manage a Golf Association with 300 members playing in 25 tournaments at different courses throughout the year and I am unsure of my design plan. My experience level with Excel is short compared to writing complete programs and the mix of programming with the use of Excel spreadsheets is a different animal.

My questions for the forum are:

1. Does a design strategy with multiple workbooks vs. a workbook with many worksheets make sense for my situation? (see below)
2. With multiple workbooks is it acceptable practice to have different people working on connected workbooks concurrently?
3. Would a single workbook with many worksheets be an acceptable practice for my situation?


The basics of my situation are:

1. There is a need for worksheets dealing with a membership roster, a waiting list roster, 25 tournament signup rosters, 25 course database sheets, 25 tournament results calculation/reporting sheets, a handicap record/calculation sheet, and 6 scorecard generation/printing sheets.

2. The tournaments are organized and managed by three man teams from a 12 man board of directors. These are the people that contract the course, get people signed up, and do all the other things necessary to run a tournament.

3. Current methods are poor, using a hodge-podge of Word, Excel, and email with no version or data control. It works but is very difficult.

4. My thoughts are that I should create separate workbooks for most of the elements mentioned above. The membership and waiting list can be in one workbook with each having its own sheet. Separate workbooks for tournament signup, course data, and tournament results, each with 25 sheets plus a template for adding more tournaments. The handicap/calculation would be one sheet in a workbook. The scorecard workbook would have a sheet for each type of tournament (6 at this time).

5. The single unique data element for the members is their handicap ID number and would be a good way to tie workbooks together. Unique IDs are possible for the courses and would cross into the signup and results workbooks. So linking data between the sheets seems very feasible.

My Current Strategy is:

1. To use VBA code in each workbook that allows independent work to be done in that book and allow connections to the other workbooks for pertinent data. In other words, I could assign different people to do different tasks independently.

2. We would purchase a Microsoft Office business plan and license all or our board members (12). This gives us access to cloud storage and a separate email account for the association. It also puts everyone on the same software, which is not the case today.

3. Our next season doesn't begin until April of next year so I have several months to develop and debug my solution.


Note: Using a database such as Access with a program developed in Visual Studio probably makes the most sense. However, there is nobody in the association that could pick up the task when I leave. Building an Excel VBA application has some of the same problems but to a smaller degree.

Note: We have looked at commercial programs designed to run tournaments but the board does not feel they mesh with some of our unique requirements and they are expensive.

Any comments or suggestions are welcome.

Chuck

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)