Executing a macro upon drop down box choice

[bonzaibanana]

I am creating a document that will be a template for others to open and
specify specific things within the letter but not change others. When
someone opens it, they will find a protected document with a drop down box.
This drop down box will have several options with a macro upon exit. This
macro needs to:
1. unprotect the document
2. insert the proper file dependent on the drop down choice made
3. make the drop down box disappear
4. reprotect the document yet allow the user to enter data into the form
fields.

Is this even possible? If so, where would I find the language necessary to
do so. I was trying to record a macro for each step in order to familiarize
myself with the language, however, when I protect the document, the recording
option goes away.

 

[Ed]

Sounds like a document with a UserForm that shows on Open,
with code to search a directory and list the files in a drop down list
control. When you hit the "Go" (or whatever) button, it grabs the chosen
file, inserts it, saves as a new doc, and adds protection. All of this is
very do-able using VBA code.

I'd recommend getting a good book on VBA for your version of Word - I have
Word 2000 Developer's Handbook, which is pretty weighty but full. You can
also simply open Word, hit ALT+F11 to open the VBA editor window, and use F1
to read the Help files about UserForms, Controls, document protection, and
more. You can also Google this newsgroup and read a ton of posts on how
this can be done.

Where are you getting the document/template requirements and specifications?

Ed

 

[bonzaibanana]

I am getting this request from upper management. And the form needs to be
very user friendly. The drop down box will list types of loans and depending
on the choice, the macro will spit out the proper paragraphs of a letter with
form fields for the user to enter interest rates, etc. I have plowed through
the help functions and am now getting serious enough to get a book. Thanks
for the suggestion.

 

[Ed]

the macro will spit out the proper paragraphs of a letter with

form fields for the user to enter interest rates,

At first, it sounded like you were going to try and find a separate file,
open it and copy/paste text from there. But you can create this as it's own
template (saved with a .dot file extension), and save in that template
several AutoText entries. (Note - I have not had to use this feature
before, so I can't get deep into it, but a Google search of this newsgroup
will get you lots of info.)

The AutoText entries, though, reside in a template, which usually is not
passed around and opened like a document. If this is to be a "Master"
document with several people using it, then you will probably have to type
all your paragraphs into the macro code and assign them into the document at
the user's choice. Another choice would be to create a table at the end of
the document, put your paragraphs in different cells, and just collapse the
table row heights to 1 point. The advantages are that you get to set the
formatting, including line breaks and font formatting, of your paragraphs
before hand; it also makes it easier to change any text. When a new
document is created and saved, the code can simply delete this table from
the new doc.

As I envision this, the hardest part is going to be getting all the little
details, especially User Input for the interest rates inserted in the right
places. I assume you want to calculate things, too - that might be a bit of
a rub as well - I'm not real up on Word calculating. And who will be the
end user of this - people in your company who will know what is happening
and can be trained to use it right? The less understanding your user is,
the more error-checking and child-proofing has to be integrated.

Open a Word document, press ALT+F11 to get into the VB Editor, then
Insert>>Form. Show the toolbox, insert a ListBox control, and play around
with making it work and adding entries. F1 brings up the Help (which may
not be very helpful), and you can Google search Groups (if you use Advanced
Groups Search, you can specify Groups as *word.vba*). (Unfortunately, I've
never used a ListBox control before either, so I'm not very much help there
right now.)

HTH
Ed

 

[Ed]

I assume you want to calculate things, too - that might be a bit of

a rub as well - I'm not real up on Word calculating.

If you want to do the calculations "on the fly" - that it, as you are
creating this document, then perhaps a better method would be to have
everything set into Excel, which has formulas and such set in place to do
all kinds of calculations, and then open a New Word doc and push all your
info into that, which can then be saved however your want. Or you could
even do it all in Excel, though creating the letter-sized and letter-looking
report "doc" is more difficult.

Ed