Solved: auto fill via dropbox selection

[Adonaioc]

I am making a form that will be used alot by many users, I want the first drop box to act like a master filling in values in the other boxes

EG.

if in the master dropbox d&b is selected.

Master-D&B is selected
1 2 and 3 autofill with the values
1-rating
2-duns#
3-name

if in the master dropbox Frieght is selected.

Master-freight is selected
1 2 and 3 autofill with the values
1-Number
2-date
3-time

I hope that makes sence BTW im using word

[fumei]

If these are formfield dropdowns (not ActiveX comboboxes) then you can use an OnExit macro.

Say the "master" is named..."Master". Your other ones (1, 2, and 3) are named Yadda1, Yadda2, Yadda3. Demo attached.

Select D & B and the other formfields have:

1-rating
2-duns#
3-name

Select Freight and the other formfields have:

1-Number
2-date
3-time

[Adonaioc]

When d & b is selected i want box one to always equal rating and box to to always equal duns# and box 3 to always equal name

Does that make sence?

[Adonaioc]

Here is what i have so far it does what i want accept that I want the 3 bottom boxes to default to the .add not blank

[lucas]

I'm not a word guy so I have never found a good use for form fields that a nice clean userform cannot just as easily replace.......Gerry and Tony can tell me where I'm wrong.........I'm sure there is a good use for them..

Attached is the way I would approach your problem.....if I understand your need.

[fumei]

Why would you have a dropdown with ONE item?

[fumei]

Speaking of formfields versus userforms...

1. formfields will accept user input in a High Security environment. It takes code to Show the userform, and if Security is High, code will not run.

2. I too prefer userforms mostly. They are easier to error trap. I still use formfields in the document (unshaded). It can make it easier to place the data from the userform, I think better than bookmarks. It depends. Remember you can use formfields via VBA without setting document protection.

[Adonaioc]

The example is good, but will not work in this application. The user is going to open the file chose an option fill in the rest of the criteria and save the file as a different file name, with this example when the copy is saved it still prompts for a name when it is opened.

It doesn't have to be a drop down box but in the future some of the questions may have 2 options, I still want to be able to set the default.

[lucas]

The example is good, but will not work in this application. The user is going to open the file chose an option fill in the rest of the criteria and save the file as a different file name, with this example when the copy is saved it still prompts for a name when it is opened.


Well, since you came here to learn, maybe it's time you read up on templates. The example is much better than you give it credit for being.

If you open my example and change this part in the thisdocument module:
Delete this part:

Private Sub Document_Open()
UserForm1.Show
End Sub

Insert this part:

Private Sub Document_New()
UserForm1.Show
End Sub

and save the file as a template(.dot) then you will get what I have attached to this post.

Just double click on the attached and use the drop down to fill in the bookmarks and close and save the file. Then re-open the new file you have saved......voila....no userform.

better yet. You can then double click the template again and create another, brand new document....again and again and again and each clone when reopened will not show the userform as it is in the template and is only called on document new.....when you clone from the template.........

ps. I had to zip up the attachment as the board software will not allow me to uload a .dot file directly.

[Adonaioc]

Beautiful, that is a perfect solution to my problem. I did not realize that you could make it only run from the template. Thank you sir, you are brilliant!

[lucas]

Not brilliant.......I've just been reading posts by people who are.....Gerry(fumie), Malcolm and Tony.....

You need to unerstand how word works in it's most basic form before you try to automate it.......

[Adonaioc]

is there a book or tutorial that you would recommend?

[lucas]

http://vbaexpress.com/training/



The "learning VBA" forum available from the forum front page has book reviews and resources...