Custom data form

[pete]

I am trying to manage a list.

I know that Excel has a built-in "Data Form" command that will do most
of what I want; well, more or less. But some of my fields are small
(like ID number which is a simple integer), while others are much
bigger (such as "notes" fields that contain a few sentences). So I want
to be able to change the size of the fields displayed on the form, move
them around the screen, and that type of thing. Ideally, I would also
like to change some of the fields to be drop-down lists rather than
text boxes - and perhaps have more than 32 fields too if these is
possible, but that is less important.

I have searched both Excel Help and these newsgroups, and cannot find a
way forward. I have spotted how to create a UserForm in the VBA editor.
I can create controls for each of my 32 fields, but this seems quite
time consuming - is there an automatic way to do it? And also, when I
create a control, I cannot find how to link it back to the list.

I guess most of what I want is either impossible (automatically
creating the form from my list) or so simple the answer is obvious
(linking fields to the list) so I must be stupid; so I am sorry to ask
this on both counts.

Thanks for any help.

Pete

 

[Bob Greenblatt]

I am trying to manage a list.

I know that Excel has a built-in "Data Form" command that will do most
of what I want; well, more or less. But some of my fields are small
(like ID number which is a simple integer), while others are much
bigger (such as "notes" fields that contain a few sentences). So I want
to be able to change the size of the fields displayed on the form, move
them around the screen, and that type of thing. Ideally, I would also
like to change some of the fields to be drop-down lists rather than
text boxes - and perhaps have more than 32 fields too if these is
possible, but that is less important.

I have searched both Excel Help and these newsgroups, and cannot find a
way forward. I have spotted how to create a UserForm in the VBA editor.
I can create controls for each of my 32 fields, but this seems quite
time consuming - is there an automatic way to do it? And also, when I
create a control, I cannot find how to link it back to the list.

I guess most of what I want is either impossible (automatically
creating the form from my list) or so simple the answer is obvious
(linking fields to the list) so I must be stupid; so I am sorry to ask
this on both counts.

Thanks for any help.

Pete

No, you are not stupid. Excel's built in data form is pretty rudimentary and
will only handle the simplest of cases. You seem to have already exceeded
its limits. The way to go is with a user form. And, no, there is no
automatic way to create this. You will need to use VBA to populate the form,
and place the data into the worksheet when the user dismisses the form. Yes,
this is time consuming, but usually is the best way to go to insure fool
proof data entry. Only you can decide if the time spent is worth the reward.

 

[Pete Loughborough]

Bob,

Thanks for the information - and the reassurance on my mental capacity.
Only one problem.

I already have the database - nearly 1,000 records. This is a database
of business contacts, and already contains names, addresses, etc.
Whenever we deal with any of our clients, I want to update the record
with any information that has changed, notes on the meeting, agreed
actions, and so on.

Your reply seemed to assume I would be populating the data from
scratch, which is not the case. I hope I do not have to type in all
1,000 addresses again!

And I still do not know how to link the control in the form to the list
itself!

The inbuilt data form would be fine - if I could change field sizes,
etc., which you confirm I cannot do. (If only I could create a form in
Excel as I do in Access - but my current company does not use Access!)
It is all very frustrating.


Pete.

 

[Bob Greenblatt]

Bob,

Thanks for the information - and the reassurance on my mental capacity.
Only one problem.

I already have the database - nearly 1,000 records. This is a database
of business contacts, and already contains names, addresses, etc.
Whenever we deal with any of our clients, I want to update the record
with any information that has changed, notes on the meeting, agreed
actions, and so on.

Your reply seemed to assume I would be populating the data from
scratch, which is not the case. I hope I do not have to type in all
1,000 addresses again!

And I still do not know how to link the control in the form to the list
itself!

The inbuilt data form would be fine - if I could change field sizes,
etc., which you confirm I cannot do. (If only I could create a form in
Excel as I do in Access - but my current company does not use Access!)
It is all very frustrating.


Pete.

No, you do not have to re-key the database. The one you have will work fine.
There are several ways to do this. But, I think, the easiest in VBA is to
use the row of the active cell to identify the database record. The VBA code
then moves the each cell in that row into the user form field during the
activate event. Code in the OK button click event then moves the form's
field contents back to the worksheet. Usually you can spiff it up with next
and previous buttons and other stuff if needed.

 

[Pete Loughborough]

Okay, I now know what Excel can do, but I still do not know HOW to do
it.

I can create a new UserForm in VBA.

I can add a control to it - say a TextBox.

I can place this TextBox where I like, and make it any size I like.

But, how do I link this to my list?

I cannot find the answer in any Help section (perhaps I just do not
know where to look!), nor can I find an example to copy.

Please help.

 

[Bob Greenblatt]

Okay, I now know what Excel can do, but I still do not know HOW to do
it.

I can create a new UserForm in VBA.

I can add a control to it - say a TextBox.

I can place this TextBox where I like, and make it any size I like.

But, how do I link this to my list?

I cannot find the answer in any Help section (perhaps I just do not
know where to look!), nor can I find an example to copy.

Please help.

As I said, the text box is NOT linked to the list. You need to write VBA
code which runs at the user form activate event to populate the text box
from a cell in the worksheet.

 

[wayne ingalls]

Okay, I now know what Excel can do, but I still do not know HOW to do
it. [snip]

Please help.

Hi Pete

I think I can help out. If you haven't done anything in VBA
before it's a bit difficult knowing where to start. If that's
your real email address in the message header, I'll send you a
separate note with my contact information.

I think it may be easiest if you send me a copy of your file
with the userform, but with a few lines of dummy information
instead of the real data. I won't have to recreate what you've
already done and I can explain what you would need to do next.

Sound OK?

-wayne