MS Word VB Code Lookup


I am trying to figure out the most effective way to do the following:

I would like to develop a single form in Word wherein selecting a specific GL code would bring show the appropriate fields and possibly populate in the related fields as directed by linked spreadsheet or code.  I could do this in access, but was hoping there is a way to do this in a word doc since I only need 1 form, it seems silly to generate an entire db w/ access page just to develop this.  I have never used VB in word before and dont know how to link it to the fields.  I have used it in Access and am fairly new to it. Perhaps someone can help walk me through it.  I am a fast learner.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Have you considered doing it all in Excel ?
Excel have an easy reference system to it's cell's and VBA (press F11 while in Excel) to control it, since you where thinking on linking to excel allready this seems like a good choice.
And you dont have to involve VB or Word at all.

If you still want to make your form in word you can put bookmarks in the form I suppose, and populate the bookmarked fields via ADO.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tessmarinAuthor Commented:
Its a possibility that I could do it in excel.  The form will need to look like a normal form structure with signature blocks, etc.  Its kind of a pain to do in excel, but it can be done.  The additional problem is the linking because its going over the intranet which would be nice to have the code in the form.  So any links that occur need to work via the intranet.  I can link it to a separate worksheet in the same spreadsheet, but I would need to find a way to hide that addl worksheet so it cant be modified or lock it from ppl.

The ADO bookmarking sounds like an option, but the amusing part is that I cant figure out how to get code into the word form in the first place.  I open the word doc and select VB code and its empty.  I dont know how to link the drop down form items to a property and insert the code like I can in Excel.  I have been searching in my books for the how to section on adding code to a control in word, but so far no luck.
tessmarinAuthor Commented:
re: I dont know how to link the drop down form items to a property and insert the code like I can in Excel.  

I meant Access not Excel sorry.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

tessmarinAuthor Commented:
I will play with excel and see what I can do, but just in case here is more info on the word vb form concept:

There are 26 possible variations to this form based on types of Activity

3 addl fields need to populate based on the selection of a specific "Activity".

This data can be housed in excel but I don’t know how to grab it.  I could just write it up in code as below, but if it changes then I can’t modify it without modifying the code.   I will only give one variation example.

Assuming the word form has 4 controls:
Field1: Activity (using the form option drop down menu)  = drop down with multiple choices
Field2: Cost Center  - text field I guess
Field3: Account – again text field
Field4: Description – text field

My original idea was to use an access data page, but it seemed like too much for a form.  Ill tell you how I would try to write this in access (minus error handling which I’m not sure applies in word etc):

Private Sub Field1_AfterUpdate()

If Field1.value = “Non-Capitol” then
Field2.value = 1234
Field3.value = 12345
Field4.value = tooling
End if

End sub

And basically draft that up for the different variations based on Field1.  It seems silly though and would be better to use excel and link with book marking if possible.
You are probably using the Word's Form set of controls, try using the visual basic control set (right click at the command bar in word and chose 'Visual Basic' then click the 'controls'  button).
Now add a button for example, then doubble click it, you should now be in VBA mode at the click event.

And it's real easy to protect and hide one or more forms in Excel, if you chose to go that way.
And about your drop down:
You can have a dedicated sheet for your dropdown info like:

ColumnA   ColumnA   ColumnA   ColumnA
Drop1       ValueZ       ValueZ     ValueX
Drop2       ValueZ       ValueY     ValueZ
Drop3   ......  and so on

Then when a item is selected just read the row values in to variables and insert them in cells:

Range("E4").Value = varA  
Range("E5").Value = varB  
Range("E6").Value = varC  

This can be done in a number of ways, but the good thing about it is that you dont have to update the code, just unlock the hidden sheet and add another row if new data comes available.
tessmarinAuthor Commented:
RE: You are probably using the Word's Form set of controls, try using the visual basic control set (right click at the command bar in word and chose 'Visual Basic' then click the 'controls'  button).
Now add a button for example, then doubble click it, you should now be in VBA mode at the click event.

LOL!!!!!!!!!!  I feel pretty dumb now..  

yes I agree the hiding thing should be easy, i just have to figure it out.  I am building the excel sheet so I can go either way with this form.  For learning purposes how do you know how to  grab the field data from excel with vb. I have not done that before.  Usually everything I need is in access.
In vb/vba you just reference the cell (field data) from the grid, like this for example (air code):

Sub GrabSomeData()
Dim iMynumbers(3) as Integer
iMynumbers(0) = Sheets("MySheet").range("B1").value
iMynumbers(1) = Sheets("MySheet").range("C1").value
iMynumbers(2) = Sheets("MySheet").range("D1").value
iMynumbers(3) = (iMynumbers(0) + iMynumbers(1)) / iMynumbers(2)
Msgbox "Result:" & iMynumbers(3)
End Sub

Getting tired, it's midnight here :)
I check in tomorrow again.

Anne TroyEast Coast ManagerCommented:
tessmarinAuthor Commented:
Okay I am scrapping the vb idea completely due to a combo box issue resolved earlier by Dreamboat.  I will need to figure out how to do this in excel instead.  Unfortunately, excel is my weakest area I am heading over to the excel board to try to find my solutions.  I will be using excel instead and thus SNilsson I will be accepting your answer.

Thank you tessmarin, and feel free to open a new low-point question for further help you might need as you start getting in to trouble, I feel like I owe you more of a solution for 500p, and good luck with your Excel work.
From Word, you can build an input dialog,  with Add.Item to display a selection in a combo box,
then implement your IF/THEN logic

here's some specifics:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.