Solved

MS Word VB Code Lookup

Posted on 2003-11-18
12
3,723 Views
Last Modified: 2010-04-17
Hello,

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.

Thanks
0
Comment
Question by:tessmarin
12 Comments
 
LVL 8

Accepted Solution

by:
SNilsson earned 500 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.
0
 

Author Comment

by:tessmarin
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.
0
 

Author Comment

by:tessmarin
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.
0
 

Author Comment

by:tessmarin
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.
0
 
LVL 8

Expert Comment

by:SNilsson
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.
0
 
LVL 8

Expert Comment

by:SNilsson
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.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:tessmarin
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.
0
 
LVL 8

Expert Comment

by:SNilsson
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.

0
 
LVL 22

Expert Comment

by:Dreamboat
ID: 9776838
0
 

Author Comment

by:tessmarin
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.

Thanks
0
 
LVL 8

Expert Comment

by:SNilsson
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.
0
 

Expert Comment

by:jenellea
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
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
copyEndy  challenge 15 58
noX challenge 17 77
Scripting vs. Programming languages 25 117
Please explain: Aspect Oriented Programming 2 57
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
This article will show, step by step, how to integrate R code into a R Sweave document
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now