Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Worksheet form design
I hope general design questions are appropriate for the forum ... if not please let me know. i work with kids for a non-profit and I'm creating a Client Info form on a worksheet. I plan to store all client info in a list on a separate worksheet and use VBA to populate the form with a particular client's current info - work on the form updating/changing/viewing client info - use VBA to update the client info list with any changed data. There is a fair amount of info for each client/record - maybe 75-100 fields, however there would only be 25-35 client/records . So I'd have: Wks 1: A Main Menu with list of clients displaying some key data - Macros to get me to individual clt form, and add or delete clients Wks 2: Hidden database storing all Clt info. Wks 3: Client info form I wanted to keep all the client data in a separate wks list rather than 1 wks form per client so i could more easily make changes to the form design. I'm a beginner at VBA - but I feel pretty confident about working out the programming - my question: Is the way I've gone about structuring the thing "good design". Thanks in advance for any feedback. Richard -- rgarber50 ------------------------------------------------------------------------ rgarber50's Profile: http://www.excelforum.com/member.php...o&userid=11350 View this thread: http://www.excelforum.com/showthread...hreadid=393518 |
#2
|
|||
|
|||
Richard,
On Fri, 5 Aug 2005 22:36:04 -0500, rgarber50 wrote: I hope general design questions are appropriate for the forum ... if not please let me know. I should mention that this isn't really a "forum", nor is it regulated or brought to you by nice Mr. Rubin. The "Forum" on the site that you posted through is just a gateway to public Usenet news groups, which you can also access by many other means. The question's therefore entirely appropriate. i work with kids for a non-profit and I'm creating a Client Info form on a worksheet. I plan to store all client info in a list on a separate worksheet and use VBA to populate the form with a particular client's current info - work on the form updating/changing/viewing client info - use VBA to update the client info list with any changed data. There is a fair amount of info for each client/record - maybe 75-100 fields, however there would only be 25-35 client/records . At the outset I'd say that I'd be more inclined to use Access for this sort of thing rather than Excel. But if you don't HAVE Access, certainly Excel can be used as a substitute, particularly if you have only 25 to 35 clients. 75 to 100 fields strikes me as odd. It seems to be too many fields for basic information like name, address, etcetera, so I have a feeling that you may be recording things like the childrens' activities in those extra columns? If so, it would mean that you'd probably have to define a specified number of activities per client... which could come back to bite you further down the track. A relational database can free you from those limitations, but there's no point in going into that too much since it's only speculation on my part. But one thing you might consider is whether you HAVE to have all of the client's information on the one row, or whether it might be more flexible to have basic client details in one row of one sheet, and "variable" data (data which may or may not apply to each client, or which may require a different number of fields for each client) on a separate sheet. You could link them together by having a code number for each client. This is a somewhat cut down version of what a relational database does. It's not a path that I'd go down if I were updating a workbook manually (it would be too hard to do data validation to ensure that the records linked together), but since you'll be doing it via VBA you shouldn't have too many problems with such an approach. However the one that you're suggesting could be just as effective, and it would be a touch easier to maintain... as long as you don't need to change the number of fields down the track. So I'd have: Wks 1: A Main Menu with list of clients displaying some key data - Macros to get me to individual clt form, and add or delete clients Wks 2: Hidden database storing all Clt info. Wks 3: Client info form I'm not a big fan of using worksheets as data entry forms; it's hard to walk the line between protection and useability. It's certainly a viable option, but I'd suggest that you look at creating a User Form instead. It gives you better control over data validation. For example, if you use Data Validation on a worksheet, the validation can generally be blown away by someone just pasting into the cell from another sheet. If you have a combo box on a user form, you can ensure that the validation remains in tact. I wanted to keep all the client data in a separate wks list rather than 1 wks form per client so i could more easily make changes to the form design. That's good thinking. I'm a beginner at VBA - but I feel pretty confident about working out the programming - my question: Is the way I've gone about structuring the thing "good design". With most things, there are many ways to get the same result, all with their own pros and cons. A bit of experimentation will tell you what's right for you. Thanks in advance for any feedback. Richard --------------------------------------------------------- Hank Scorpio scorpionet who hates spam is at iprimus.com.au (You know what to do.) * Please keep all replies in this Newsgroup. Thanks! * |
#3
|
|||
|
|||
Hank I really appreciate your time and thoughtful suggestions. I went back and I looked more closely at the number of fields and it would be more like 40. Most are things like name, address, school, grade, due dates for different reports etc. Several cry for a relational approach. Such as: school contacts - generally there is one - but its not unusual to have more. Siblings and age - this can range from 0 to as many as 8 or 10. Some kids are court ordered - and if they are it might be because they are on probation or because the parents did something. Depending on the situation it would require varying numbers of social workers and/or a probation officer. I like your idea of creating several worksheets for this kind of variable info. And if I create a user form with tabs and some combo boxes it would certainly be a lot more flexible. I have been wanting to have a field for general notes and I was thinking of just using a scrolling text box. But your idea of seperate wks's for variable info gave me the idea of dating each note and listing each one as a separate record in a list. Then I could populate a userform with this list of dated notes/history which i think would be even more useful. One more thing - do you see any reason to keep all the data on a separate workbook? I only think of this because if i were to update the form or programming then it strikes me as easier to just replace this front end workbook. Thanks again for your help - I really enjoy learning about programming and you helped me get a new concept (that i can think relationally in excel). Richard Hank Scorpio Wrote: Richard, On Fri, 5 Aug 2005 22:36:04 -0500, rgarber50 wrote: I hope general design questions are appropriate for the forum ... if not please let me know. I should mention that this isn't really a "forum", nor is it regulated or brought to you by nice Mr. Rubin. The "Forum" on the site that you posted through is just a gateway to public Usenet news groups, which you can also access by many other means. The question's therefore entirely appropriate. i work with kids for a non-profit and I'm creating a Client Info form on a worksheet. I plan to store all client info in a list on a separate worksheet and use VBA to populate the form with a particular client's current info - work on the form updating/changing/viewing client info - use VBA to update the client info list with any changed data. There is a fair amount of info for each client/record - maybe 75-100 fields, however there would only be 25-35 client/records . At the outset I'd say that I'd be more inclined to use Access for this sort of thing rather than Excel. But if you don't HAVE Access, certainly Excel can be used as a substitute, particularly if you have only 25 to 35 clients. 75 to 100 fields strikes me as odd. It seems to be too many fields for basic information like name, address, etcetera, so I have a feeling that you may be recording things like the childrens' activities in those extra columns? If so, it would mean that you'd probably have to define a specified number of activities per client... which could come back to bite you further down the track. A relational database can free you from those limitations, but there's no point in going into that too much since it's only speculation on my part. But one thing you might consider is whether you HAVE to have all of the client's information on the one row, or whether it might be more flexible to have basic client details in one row of one sheet, and "variable" data (data which may or may not apply to each client, or which may require a different number of fields for each client) on a separate sheet. You could link them together by having a code number for each client. This is a somewhat cut down version of what a relational database does. It's not a path that I'd go down if I were updating a workbook manually (it would be too hard to do data validation to ensure that the records linked together), but since you'll be doing it via VBA you shouldn't have too many problems with such an approach. However the one that you're suggesting could be just as effective, and it would be a touch easier to maintain... as long as you don't need to change the number of fields down the track. So I'd have: Wks 1: A Main Menu with list of clients displaying some key data - Macros to get me to individual clt form, and add or delete clients Wks 2: Hidden database storing all Clt info. Wks 3: Client info form I'm not a big fan of using worksheets as data entry forms; it's hard to walk the line between protection and useability. It's certainly a viable option, but I'd suggest that you look at creating a User Form instead. It gives you better control over data validation. For example, if you use Data Validation on a worksheet, the validation can generally be blown away by someone just pasting into the cell from another sheet. If you have a combo box on a user form, you can ensure that the validation remains in tact. I wanted to keep all the client data in a separate wks list rather than 1 wks form per client so i could more easily make changes to the form design. That's good thinking. I'm a beginner at VBA - but I feel pretty confident about working out the programming - my question: Is the way I've gone about structuring the thing "good design". With most things, there are many ways to get the same result, all with their own pros and cons. A bit of experimentation will tell you what's right for you. Thanks in advance for any feedback. Richard --------------------------------------------------------- Hank Scorpio scorpionet who hates spam is at iprimus.com.au (You know what to do.) * Please keep all replies in this Newsgroup. Thanks! * -- rgarber50 ------------------------------------------------------------------------ rgarber50's Profile: http://www.excelforum.com/member.php...o&userid=11350 View this thread: http://www.excelforum.com/showthread...hreadid=393518 |
#4
|
|||
|
|||
On Sat, 6 Aug 2005 08:18:06 -0500, rgarber50
wrote: Hank I really appreciate your time and thoughtful suggestions. You're welcome... [Snip] One more thing - do you see any reason to keep all the data on a separate workbook? I only think of this because if i were to update the form or programming then it strikes me as easier to just replace this front end workbook. I don't really think so; you'd add the extra complication of having to ensure that both the front end workbook and the back end data workbook were open when the user's doing data entry (which, granted, could be automated easily enough), and you wouldn't save any speed in loading. What you have in mind could also be achieved by making sure (as far as is possible[1]) that all of the relevant data entry code was contained un the User Form code module rather than in .bas modules. If you can put ALL of the code in that module, you end up with an "encapsulated" front end which can be easily swapped in and out of the workbook by simply deleting the old user form, and importing the new one. If you still choose to use a worksheet rather than a user form as your front end, you can achieve the same result by having all of the code in the sheet's module. Either way, it keeps your front end encapsulated and easily updatable. ([1] There are very few things that can't be done by code in the class module as opposed to a standard (.bas) module. Off the top of my head the only one I can think of is when you need to create a callback function for an API call, but you probably won't be getting that complex.) Thanks again for your help - I really enjoy learning about programming and you helped me get a new concept (that i can think relationally in excel). Enjoy! --------------------------------------------------------- Hank Scorpio scorpionet who hates spam is at iprimus.com.au (You know what to do.) * Please keep all replies in this Newsgroup. Thanks! * |
#5
|
|||
|
|||
Ohhh! I get it - if I keep all the relevant user form code in the userform module - it should be relatively easy to update. The first iteration will be a wks form - only because i have work to do and that will certainly be quicker for me - but then I'm going to give the userform approach a try - should be fun. I'll keep you posted! Thanks again. Richard Hank Scorpio Wrote: On Sat, 6 Aug 2005 08:18:06 -0500, rgarber50 wrote: Hank I really appreciate your time and thoughtful suggestions. You're welcome... [Snip] One more thing - do you see any reason to keep all the data on a separate workbook? I only think of this because if i were to update the form or programming then it strikes me as easier to just replace this front end workbook. I don't really think so; you'd add the extra complication of having to ensure that both the front end workbook and the back end data workbook were open when the user's doing data entry (which, granted, could be automated easily enough), and you wouldn't save any speed in loading. What you have in mind could also be achieved by making sure (as far as is possible[1]) that all of the relevant data entry code was contained un the User Form code module rather than in .bas modules. If you can put ALL of the code in that module, you end up with an "encapsulated" front end which can be easily swapped in and out of the workbook by simply deleting the old user form, and importing the new one. If you still choose to use a worksheet rather than a user form as your front end, you can achieve the same result by having all of the code in the sheet's module. Either way, it keeps your front end encapsulated and easily updatable. ([1] There are very few things that can't be done by code in the class module as opposed to a standard (.bas) module. Off the top of my head the only one I can think of is when you need to create a callback function for an API call, but you probably won't be getting that complex.) Thanks again for your help - I really enjoy learning about programming and you helped me get a new concept (that i can think relationally in excel). Enjoy! --------------------------------------------------------- Hank Scorpio scorpionet who hates spam is at iprimus.com.au (You know what to do.) * Please keep all replies in this Newsgroup. Thanks! * -- rgarber50 ------------------------------------------------------------------------ rgarber50's Profile: http://www.excelforum.com/member.php...o&userid=11350 View this thread: http://www.excelforum.com/showthread...hreadid=393518 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting up a form to input into selected worksheet | Excel Discussion (Misc queries) | |||
Only calculate specific worksheet automatically? | Excel Worksheet Functions | |||
Worksheet Function - Find? | Excel Worksheet Functions | |||
grand total column B from every worksheet in workbook | Excel Discussion (Misc queries) | |||
Heps to design Locked/Unlocked cells in protected worksheet | Excel Discussion (Misc queries) |