Worksheet form design

[rgarber50]

I hope general design questions are appropriate for the forum ... if no
please let me know.

i work with kids for a non-profit and I'm creating a Client Info for
on a worksheet. I plan to store all client info in a list on a separat
worksheet and use VBA to populate the form with a particular client'
current info - work on the form updating/changing/viewing client inf
- use VBA to update the client info list with any changed data. Ther
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 tha
1 wks form per client so i could more easily make changes to the for
design.

I'm a beginner at VBA - but I feel pretty confident about working ou
the programming - my question: Is the way I've gone about structurin
the thing "good design".

Thanks in advance for any feedback.
Richar

 

[Hank Scorpio]

Richard,

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.

 

[rgarber50]

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]

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!

 

[rgarber50]

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