Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


MS Word VB Code Lookup

Posted on 2003-11-18
Medium Priority
Last Modified: 2010-04-17

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.

Question by:tessmarin
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

SNilsson earned 2000 total points
ID: 9774225
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.

Author Comment

ID: 9774296
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.

Author Comment

ID: 9774306
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.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 9774354
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.

Expert Comment

ID: 9774423
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.

Expert Comment

ID: 9774522
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.

Author Comment

ID: 9774560
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.

Expert Comment

ID: 9774675
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.

LVL 22

Expert Comment

ID: 9776838

Author Comment

ID: 9780970
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 ...so 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.


Expert Comment

ID: 9781160
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.

Expert Comment

ID: 12589695
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:  http://www.fontstuff.com/vba/vbatut09c.htm

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn different types of Android Layout and some basics of an Android App.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question