MS Word VB Code Lookup

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.

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.

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.

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.

Dunno if this helps, Tess:

http://www.mvps.org/word/FAQs/InterDev/FillListBoxFromXLDAO.htm

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

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