AutoComplete form macro

[TechieRob]

This seems to be a basic solution but I cant for the life of me figure out
how to do it in word....

Ive created a standard form with a dozen or so "standard text" form entries
and 3 "drop down" form entries. I have 2 options for each of the drop down
forms. What I am trying to do is create a 'simple' macro or similar that will
set all drop down forms to the same value based on an initial selection...

That is; user selects option 1 from the first drop down list and all drop
down forms get set to this option. User selects option 2 from the first drop
down form and all other forms get set to option 2.

I know it sounds like a peculiar request for such a small number of forms,
but if anyone works for someone in upper management, then you would know
committed they are to making things "simpler" for them

Thanks in advance

 

[Helmut Weber]

Hi,
like this:

Dim sFrm As String ' name of formfield
Dim oFrm As FormField ' object of kind formfield
sFrm = Selection.FormFields(1).Name
For Each oFrm In ActiveDocument.FormFields
If oFrm.Name <> sFrm Then
If oFrm.Type = wdFieldFormDropDown Then
oFrm.Result = Selection.FormFields(1).Result
End If
End If
Next

Unfortunately, for a dropdownformfield there seems
to be to no event that triggers immediately after
a different list-entry is selected. So the update
of the other formfields will take place after leaving
the field in question, if you put
the code in on onexitmacro, of course.

Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/

 

[TechieRob]

This 'kind of' works in regards that all the dropdown forms change; but they
all change to the exact word that is in the selected position. I need for it
to simply change to whatever word is in that position.

For example say that we have 2 forms A and B with 2 selections:
A B
1 Dog 1 Bark
2 Cat 2 Meow

Currently in the macro if I select "Dog", form 2 is also set to "Dog". The
same thing happens if we select "Cat" (in that form 2 gets set to "Cat")

What I need is for when the user selects "Dog" from the first form that the
second form gets set to "Bark".

Can this be done?

 

[Helmut Weber]

Hi Rob,
like this, with improvements welcome:

Sub Test662()
' wdFieldFormDropDown = 83 (I like it short)
Dim iDrp As Integer ' index of entry selected
Dim sFrm As String ' name of formfield
Dim oFrm As FormField ' object of kind formfield
Dim sRsl As String ' result of selected formfield

If Selection.FormFields(1).Type <> 83 Then
Exit Sub ' just in case
End If
sFrm = Selection.FormFields(1).Name
sRsl = Selection.FormFields(1).Result
' get the index of the selected item
With Selection.FormFields(1).DropDown.ListEntries
For iDrp = 1 To .Count
If .Item(iDrp).Name = sRsl Then Exit For
Next
End With
' set the result of all other formfields of type 83
' to the selected listentry of the selected formfield
' of type 83
For Each oFrm In ActiveDocument.FormFields
If oFrm.Name <> sFrm Then
If oFrm.Type = 83 Then
oFrm.Result = oFrm.DropDown.ListEntries(iDrp).Name
End If
End If
Next
End Sub

HTH
Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/

 

[TechieRob]

Fantastic; thats fixed it

Thanks a lot

 

[angelita]

hi Helmut,

this is fantastic! works like a charm. very useful for our purpose. at
the risk of asking too much, is there a way to tweak this code so that
the form field looks for dropdown info off of XL spreadsheet such as
the example below:

Sub GetPrCode()

Dim db As DAO.Database
Dim rs As DAO.recordset
Dim NoOfRecords As Long

' Open the database
Set db = OpenDatabase("C:\wordfiles\Templates\office forms\Elite
Data File.xls", False, False, "Excel 8.0")

' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM [Practice Code$A2:A80]")

' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With

' Set the number of Columns = number of Fields in recordset
'frmData.cmbData. = rs.Fields.Count

' Load the ListBox with the retrieved records
'ListBox1.Column = rs.GetRows(NoOfRecords)
frmData.lblRandom.Caption = "Please Select the Appropriate Practice
Code from the List Below"
frmData.cmbData.Column = rs.GetRows(NoOfRecords)
frmData.Show
' Cleanup
rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

End Sub

Is this doable? The reason being is that our users would probably need
to add their own data (outside of what's available in the dropdown);
hence, it's not limited to list or <MATCH REQUIRED> = "False".

Thanks very much,
Angelita

 

[carlosriver24]

Hi Helmut,

I seem to be having nearly the same issues as the users on this blog, but Ican not figure out my issue. Maybe you can help me out?

I have 60 drop downs that each have three selection options, "Add", "Delete" and " " (drop down selection is left blank). Now, I have all the drop downs linked to one drop down box. This is used so that if the user wants to select "Add" the macro will run and fire the action to fill "Add" in the 60 drop downs.

My macro works perfectly, but my issue is that it will only fire once I click out of the box or hit tab. Also, Word is very slow in filling in all ofthe drop downs.

Is there any way to make this work faster and without the extra click??? Any help is much appreciated. Thanks in advance.

Here is my current code:

Sub OnExitDD3()
'fills text field based on content of _
dropdown field

Dim oFld As FormFields
Set oFld = ActiveDocument.FormFields
Select Case oFld("READONLY").Result

Case Is = "Add"
oFld("View1").Result = "Add"
oFld("View2").Result = "Add"
oFld("View3").Result = "Add"

Case Else
'Do nothing
End Select
End Sub

 

[Stefan Blom]

You have replied to a very old thread. You are more likely to get assistance
with this if you ask in the Word programming forum at Answers:
http://answers.microsoft.com/en-us/office/forum/customize?page=1&tab=all.

--
Stefan Blom
Microsoft Word MVP