Auto completion based on selection of Drop down menu item (Word)

I have a table/form in word where I have a drop down menu in one column. I
would like other columns to be automatically completed based on the item
selected in the drop down menu. i.e. if you select Product 1 (in the column
with the drop down), I want the product description to auto-fill in the next
column, the product cost to auto-fill in the next column, etc.

Any ideas on how to do this?

On Wed, 11 Apr 2007 15:00:01 -0700, lisa
wrote:

I have a table/form in word where I have a drop down menu in one column. I
would like other columns to be automatically completed based on the item
selected in the drop down menu. i.e. if you select Product 1 (in the column
with the drop down), I want the product description to auto-fill in the next
column, the product cost to auto-fill in the next column, etc.

Any ideas on how to do this?

There are many ways to do it. Probably the hardest part is deciding
which way is most appropriate for how you expect the form to be used.

The mechanism of populating the rest of a table row when the user
chooses a dropdown item is fairly simple. You need a macro that looks
at the current value of the dropdown, looks up the data for the rest
of the items, and places that data in the proper cells or fields. The
locations may be marked by bookmarks, or you may use the row/column
addresses of particular cells of the table. In the dropdown's
Properties dialog, you choose the macro to be the field's Exit macro,
and it runs when the user tabs away from the dropdown. There are some
sticky bits in the implementation, but those can be worked around.

The big issue is that the data (product names, descriptions, prices,
whatever else) must be stored somewhere, so it can be pulled in to
populate the table cells or fields at the right time. Your choice of
where it's stored and how it's accessed should be based on the answers
to questions like these:

- Where will the form be filled out? Only on one computer; on
several/many computers that all have access to a central server; or on
computers that may not be connected to the server?

- How much data will there be? A couple of dozen items, a hundred or
more, or thousands?

- How will the completed form be used? Printed; saved to a server;
emailed to someone; something else? Will the selections be entered
into a database or spreadsheet?

- How often will the data change? In other words, how important is it
to be able to edit the list of data easily?

- Are there any situations where users will have to enter something
that isn't available in the data? How should that be handled for form
entry and form processing?

In general terms, if the amount of data is small, it can be stored
directly in the template as "document variables" (these are invisible
storage areas that are managed by a macro). This would also be the
best choice, even for larger amounts of data, if the users can't be
assumed to connect to a central server; the ability to keep everything
in one file becomes important then. The disadvantage is that it
becomes difficult to edit the data when something changes.

Another consideration is that the dropdown form field can't handle
more than 25 items; if there are more than that, you need to create a
UserForm (a custom dialog). Read
http://www.word.mvps.org/FAQs/Userfo...eAUserForm.htm and
http://support.microsoft.com/?kbid=306258.

If the amount of data is larger than a few dozen items, and the users
are all connected to the same server, then the data can be stored in
an external file. That can be a text file, a Word document, an Excel
worksheet, a database... whatever is appropriate for the amount of
data and your comfort level in working with it. This has the advantage
of letting you manage and edit the data. It has the disadvantage that
the form can't be used by anyone who isn't connected to wherever the
data is stored. Your macro would also have to be at least a little
more complicated in the part where it looks up the data needed to
complete the form.

Finally, if the data is stored in a database, you may not need Word at
all -- every database has some way of presenting forms for entering
choices, and some way of printing the results.

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

I too have the same issue, MS word 2007. Program dropdown menu options to update from depending on selection. For example if US is selected update 5 other dropdown menus with $



Jay Freedman wrote:

Auto completion based on selection of Drop down menu item (Word)
11-Apr-07

On Wed, 11 Apr 2007 15:00:01 -0700, lisa
wrote:


There are many ways to do it. Probably the hardest part is deciding
which way is most appropriate for how you expect the form to be used.

The mechanism of populating the rest of a table row when the user
chooses a dropdown item is fairly simple. You need a macro that looks
at the current value of the dropdown, looks up the data for the rest
of the items, and places that data in the proper cells or fields. The
locations may be marked by bookmarks, or you may use the row/column
addresses of particular cells of the table. In the dropdown's
Properties dialog, you choose the macro to be the field's Exit macro,
and it runs when the user tabs away from the dropdown. There are some
sticky bits in the implementation, but those can be worked around.

The big issue is that the data (product names, descriptions, prices,
whatever else) must be stored somewhere, so it can be pulled in to
populate the table cells or fields at the right time. Your choice of
where it's stored and how it's accessed should be based on the answers
to questions like these:

- Where will the form be filled out? Only on one computer; on
several/many computers that all have access to a central server; or on
computers that may not be connected to the server?

- How much data will there be? A couple of dozen items, a hundred or
more, or thousands?

- How will the completed form be used? Printed; saved to a server;
emailed to someone; something else? Will the selections be entered
into a database or spreadsheet?

- How often will the data change? In other words, how important is it
to be able to edit the list of data easily?

- Are there any situations where users will have to enter something
that isn't available in the data? How should that be handled for form
entry and form processing?

In general terms, if the amount of data is small, it can be stored
directly in the template as "document variables" (these are invisible
storage areas that are managed by a macro). This would also be the
best choice, even for larger amounts of data, if the users can't be
assumed to connect to a central server; the ability to keep everything
in one file becomes important then. The disadvantage is that it
becomes difficult to edit the data when something changes.

Another consideration is that the dropdown form field can't handle
more than 25 items; if there are more than that, you need to create a
UserForm (a custom dialog). Read
http://www.word.mvps.org/FAQs/Userfo...eAUserForm.htm and
http://support.microsoft.com/?kbid=306258.

If the amount of data is larger than a few dozen items, and the users
are all connected to the same server, then the data can be stored in
an external file. That can be a text file, a Word document, an Excel
worksheet, a database... whatever is appropriate for the amount of
data and your comfort level in working with it. This has the advantage
of letting you manage and edit the data. It has the disadvantage that
the form can't be used by anyone who isn't connected to wherever the
data is stored. Your macro would also have to be at least a little
more complicated in the part where it looks up the data needed to
complete the form.

Finally, if the data is stored in a database, you may not need Word at
all -- every database has some way of presenting forms for entering
choices, and some way of printing the results.

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

Previous Posts In This Thread:

On Wednesday, April 11, 2007 6:00 PM
lis wrote:

Auto completion based on selection of Drop down menu item (Word)
I have a table/form in word where I have a drop down menu in one column. I
would like other columns to be automatically completed based on the item
selected in the drop down menu. i.e. if you select Product 1 (in the column
with the drop down), I want the product description to auto-fill in the next
column, the product cost to auto-fill in the next column, etc.

Any ideas on how to do this?

On Wednesday, April 11, 2007 10:05 PM
Jay Freedman wrote:

Auto completion based on selection of Drop down menu item (Word)
On Wed, 11 Apr 2007 15:00:01 -0700, lisa
wrote:


There are many ways to do it. Probably the hardest part is deciding
which way is most appropriate for how you expect the form to be used.

The mechanism of populating the rest of a table row when the user
chooses a dropdown item is fairly simple. You need a macro that looks
at the current value of the dropdown, looks up the data for the rest
of the items, and places that data in the proper cells or fields. The
locations may be marked by bookmarks, or you may use the row/column
addresses of particular cells of the table. In the dropdown's
Properties dialog, you choose the macro to be the field's Exit macro,
and it runs when the user tabs away from the dropdown. There are some
sticky bits in the implementation, but those can be worked around.

The big issue is that the data (product names, descriptions, prices,
whatever else) must be stored somewhere, so it can be pulled in to
populate the table cells or fields at the right time. Your choice of
where it's stored and how it's accessed should be based on the answers
to questions like these:

- Where will the form be filled out? Only on one computer; on
several/many computers that all have access to a central server; or on
computers that may not be connected to the server?

- How much data will there be? A couple of dozen items, a hundred or
more, or thousands?

- How will the completed form be used? Printed; saved to a server;
emailed to someone; something else? Will the selections be entered
into a database or spreadsheet?

- How often will the data change? In other words, how important is it
to be able to edit the list of data easily?

- Are there any situations where users will have to enter something
that isn't available in the data? How should that be handled for form
entry and form processing?

In general terms, if the amount of data is small, it can be stored
directly in the template as "document variables" (these are invisible
storage areas that are managed by a macro). This would also be the
best choice, even for larger amounts of data, if the users can't be
assumed to connect to a central server; the ability to keep everything
in one file becomes important then. The disadvantage is that it
becomes difficult to edit the data when something changes.

Another consideration is that the dropdown form field can't handle
more than 25 items; if there are more than that, you need to create a
UserForm (a custom dialog). Read
http://www.word.mvps.org/FAQs/Userfo...eAUserForm.htm and
http://support.microsoft.com/?kbid=306258.

If the amount of data is larger than a few dozen items, and the users
are all connected to the same server, then the data can be stored in
an external file. That can be a text file, a Word document, an Excel
worksheet, a database... whatever is appropriate for the amount of
data and your comfort level in working with it. This has the advantage
of letting you manage and edit the data. It has the disadvantage that
the form can't be used by anyone who isn't connected to wherever the
data is stored. Your macro would also have to be at least a little
more complicated in the part where it looks up the data needed to
complete the form.

Finally, if the data is stored in a database, you may not need Word at
all -- every database has some way of presenting forms for entering
choices, and some way of printing the results.

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.


Submitted via EggHeadCafe - Software Developer Portal of Choice
BizTalk Flat Text File Processing
http://www.eggheadcafe.com/tutorials...t-file-pr.aspx

See the article "How to create a Userform" at:

http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm

and the following pages of fellow MVP Greg Maxey's website :

http://gregmaxey.mvps.org/Create_and...a_UserForm.htm

http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm

particularly the part about cascading listboxes

--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my services on
a paid professional basis.

"CK Isssa" wrote in message ...
I too have the same issue, MS word 2007. Program dropdown menu options to
update from depending on selection. For example if US is selected update 5
other dropdown menus with $



Jay Freedman wrote:

Auto completion based on selection of Drop down menu item (Word)
11-Apr-07

On Wed, 11 Apr 2007 15:00:01 -0700, lisa
wrote:


There are many ways to do it. Probably the hardest part is deciding
which way is most appropriate for how you expect the form to be used.

The mechanism of populating the rest of a table row when the user
chooses a dropdown item is fairly simple. You need a macro that looks
at the current value of the dropdown, looks up the data for the rest
of the items, and places that data in the proper cells or fields. The
locations may be marked by bookmarks, or you may use the row/column
addresses of particular cells of the table. In the dropdown's
Properties dialog, you choose the macro to be the field's Exit macro,
and it runs when the user tabs away from the dropdown. There are some
sticky bits in the implementation, but those can be worked around.

The big issue is that the data (product names, descriptions, prices,
whatever else) must be stored somewhere, so it can be pulled in to
populate the table cells or fields at the right time. Your choice of
where it's stored and how it's accessed should be based on the answers
to questions like these:

- Where will the form be filled out? Only on one computer; on
several/many computers that all have access to a central server; or on
computers that may not be connected to the server?

- How much data will there be? A couple of dozen items, a hundred or
more, or thousands?

- How will the completed form be used? Printed; saved to a server;
emailed to someone; something else? Will the selections be entered
into a database or spreadsheet?

- How often will the data change? In other words, how important is it
to be able to edit the list of data easily?

- Are there any situations where users will have to enter something
that isn't available in the data? How should that be handled for form
entry and form processing?

In general terms, if the amount of data is small, it can be stored
directly in the template as "document variables" (these are invisible
storage areas that are managed by a macro). This would also be the
best choice, even for larger amounts of data, if the users can't be
assumed to connect to a central server; the ability to keep everything
in one file becomes important then. The disadvantage is that it
becomes difficult to edit the data when something changes.

Another consideration is that the dropdown form field can't handle
more than 25 items; if there are more than that, you need to create a
UserForm (a custom dialog). Read
http://www.word.mvps.org/FAQs/Userfo...eAUserForm.htm and
http://support.microsoft.com/?kbid=306258.

If the amount of data is larger than a few dozen items, and the users
are all connected to the same server, then the data can be stored in
an external file. That can be a text file, a Word document, an Excel
worksheet, a database... whatever is appropriate for the amount of
data and your comfort level in working with it. This has the advantage
of letting you manage and edit the data. It has the disadvantage that
the form can't be used by anyone who isn't connected to wherever the
data is stored. Your macro would also have to be at least a little
more complicated in the part where it looks up the data needed to
complete the form.

Finally, if the data is stored in a database, you may not need Word at
all -- every database has some way of presenting forms for entering
choices, and some way of printing the results.

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

Previous Posts In This Thread:

On Wednesday, April 11, 2007 6:00 PM
lis wrote:

Auto completion based on selection of Drop down menu item (Word)
I have a table/form in word where I have a drop down menu in one column.
I
would like other columns to be automatically completed based on the item
selected in the drop down menu. i.e. if you select Product 1 (in the
column
with the drop down), I want the product description to auto-fill in the
next
column, the product cost to auto-fill in the next column, etc.

Any ideas on how to do this?

On Wednesday, April 11, 2007 10:05 PM
Jay Freedman wrote:

Auto completion based on selection of Drop down menu item (Word)
On Wed, 11 Apr 2007 15:00:01 -0700, lisa
wrote:


There are many ways to do it. Probably the hardest part is deciding
which way is most appropriate for how you expect the form to be used.

The mechanism of populating the rest of a table row when the user
chooses a dropdown item is fairly simple. You need a macro that looks
at the current value of the dropdown, looks up the data for the rest
of the items, and places that data in the proper cells or fields. The
locations may be marked by bookmarks, or you may use the row/column
addresses of particular cells of the table. In the dropdown's
Properties dialog, you choose the macro to be the field's Exit macro,
and it runs when the user tabs away from the dropdown. There are some
sticky bits in the implementation, but those can be worked around.

The big issue is that the data (product names, descriptions, prices,
whatever else) must be stored somewhere, so it can be pulled in to
populate the table cells or fields at the right time. Your choice of
where it's stored and how it's accessed should be based on the answers
to questions like these:

- Where will the form be filled out? Only on one computer; on
several/many computers that all have access to a central server; or on
computers that may not be connected to the server?

- How much data will there be? A couple of dozen items, a hundred or
more, or thousands?

- How will the completed form be used? Printed; saved to a server;
emailed to someone; something else? Will the selections be entered
into a database or spreadsheet?

- How often will the data change? In other words, how important is it
to be able to edit the list of data easily?

- Are there any situations where users will have to enter something
that isn't available in the data? How should that be handled for form
entry and form processing?

In general terms, if the amount of data is small, it can be stored
directly in the template as "document variables" (these are invisible
storage areas that are managed by a macro). This would also be the
best choice, even for larger amounts of data, if the users can't be
assumed to connect to a central server; the ability to keep everything
in one file becomes important then. The disadvantage is that it
becomes difficult to edit the data when something changes.

Another consideration is that the dropdown form field can't handle
more than 25 items; if there are more than that, you need to create a
UserForm (a custom dialog). Read
http://www.word.mvps.org/FAQs/Userfo...eAUserForm.htm and
http://support.microsoft.com/?kbid=306258.

If the amount of data is larger than a few dozen items, and the users
are all connected to the same server, then the data can be stored in
an external file. That can be a text file, a Word document, an Excel
worksheet, a database... whatever is appropriate for the amount of
data and your comfort level in working with it. This has the advantage
of letting you manage and edit the data. It has the disadvantage that
the form can't be used by anyone who isn't connected to wherever the
data is stored. Your macro would also have to be at least a little
more complicated in the part where it looks up the data needed to
complete the form.

Finally, if the data is stored in a database, you may not need Word at
all -- every database has some way of presenting forms for entering
choices, and some way of printing the results.

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.


Submitted via EggHeadCafe - Software Developer Portal of Choice
BizTalk Flat Text File Processing
http://www.eggheadcafe.com/tutorials...t-file-pr.aspx