Populate a legacy textbox control using a legacy drop down control

[Excel_VBA_Newb]

I would like to populate a phone number into a legacy textbox control, based
on a selection from a legacy dropdown control (employee). I don't see where
there is any change events on the drop down control, in the VBA IDE. So,
would I need to perform this by using a macro, and then use the "run on exit"
option?

 

[Doug Robbins - Word MVP]

Use a macro that runs on exit from the dropdown to set the .Result of the
textinput Formfield to the .Result of the dropdown FormField.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Gordon Bentley-Mix on news.microsoft.com]

I'm making a few assumptions here, so forgive me if I get it wrong. For
instance, in spite of your username being 'Excel_VBA_Newb', since you've
posted in the Word programming NG I'm assuming you're working in Word. Also,
since you refer to 'legacy' controls, I'm assuming you're running Word 2007 -
although AFAIK it doesn't really make any difference when working with these
controls. I'm also assuming that your talking about working with form fields
in a Word document or template and not VBA UserForm controls. And after all
that, if my assumptions are correct, the answer is probably going to be a bit
of a disappointment. ;-P

You are quite correct that the only 'event' that can be used with form
fields is the Exit event, which is accessed through the 'Run macro on - Exit'
functionality available through the form field's properties. The newer
ActiveX controls may offer other methods, and the Content Control in Word
2007 might have even more, but as I have no experience working with these I'm
afraid I can't provide much direction. (And I realise that they might not be
suitable to your application anyway.)
--
Cheers!

Gordon Bentley-Mix
Word MVP

Please post all follow-ups to the newsgroup.

Read the original version of this post in the Office Discussion Groups - no
membership required!

 

[Excel_VBA_Newb]

Gordon,

Your assumptions are safe:

The username was created many a day ago, I guess I didn't think how it would
apply to later posts - in different Groups. This does indeed apply to Office
Word 2007.


Can you please tell me what the diference is between a VBA UserForm Control
and Form Fields in Word? I'm selecting the Developer tab, clicking on the
Controls Group, then selecting the "Legacy Forms" option.

Thanks for your time.

 

[Gordon Bentley-Mix on news.microsoft.com]

You're definitely working with form fields. UserForm controls are those
things you put into a UserForm created in the VBE. See:
http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm and
http://gregmaxey.mvps.org/Create_and_employ_a_UserForm.htm for more
information.
--
Cheers!

Gordon Bentley-Mix
Word MVP

Please post all follow-ups to the newsgroup.

Read the original version of this post in the Office Discussion Groups - no
membership required!

 

[VBA_Newb]

Thanks Doug and Greg, It's now working!

If you have the employee names and their phone numbers in a two column table
in a document saved with the name Employees.doc
and in your form, the dropdown formfield that will display the employee
names is given the bookmark "Employees" and the textbox that will display
the phone number is given teh bookmark "Phone", and if you run the following
macro a on entry to the Employees formfield and the macro b on entry to the
Phone formfield, the phone number corresponding to the selection that is
made in the dropdown will be displayed in the text box:

Sub a()
Dim SourceDoc As Document, myform As Document
Dim i As Long
Dim employee As Range
Set myform = ActiveDocument
With myform.FormFields("employees").DropDown
For i = .ListEntries.Count To 1 Step -1
.ListEntries(i).Delete
Next i
End With
Set SourceDoc = Documents.Open("C:\Users\Doug
Robbins\Documents\Employees.doc")
With SourceDoc.Tables(1)
For i = 2 To .Rows.Count
Set employee = .Cell(i, 1).Range
employee.End = employee.End - 1
myform.FormFields("Employees").DropDown.ListEntries.Add employee
Set employee = Nothing
Next i
End With
SourceDoc.Close
Set SourceDoc = Nothing
myform.Activate
End Sub

Sub b()
Dim SourceDoc As Document, myform As Document
Dim i As Long
Dim Phone As Range
Set myform = ActiveDocument
i = myform.FormFields("Employees").DropDown.Value
Set SourceDoc = Documents.Open("C:\Users\Doug
Robbins\Documents\Employees.doc")
Set Phone = SourceDoc.Tables(1).Cell(i + 1, 2).Range
Phone.End = Phone.End - 1
myform.FormFields("Phone").Result = Phone.Text
Set Phone = Nothing
SourceDoc.Close
Set SourceDoc = Nothing
myform.Activate
End Sub

You will need to change the path in

Documents.Open("C:\Users\Doug Robbins\Documents\Employees.doc")

to suit where you save the Employees.doc file.


--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my services on
a paid professional basis.