Update dropdown boxes 1

[Vittles]

I am setting up templates in Word 2000 and use the same dropdown boxes that list contact names for many of these. As people come & go, I have to update each one.

Is there a way to connect all of these to a list of values contained in another doc or excel sheet so that I only have to change one area & have the rest update automatically? I am thinking of a function similar to what Access has where you have a table and the combo box values come from that table... Any ideas?

 

[rdeleon]

I have used this is the past. I don't know if it's the best way, but it works. You need to reference the Excel object to get it to work. Also the workbook Users.xls has a named range called "UserList" that is just a list of names. Hope it helps.

Private Sub Document_Open()

Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oRange As Excel.Range
Dim i As Integer

Set oXL = New Excel.Application
Set oWB = oXL.Workbooks.Open("c:\temp\users.xls")

Set oRange = oWB.Worksheets(1).Range("UserList")

For i = 1 To oRange.Count
If oRange.Cells(i, 1) <> "" Then
Me.ComboBox1.AddItem oRange.Cells(i, 1)
End If
Next i

oWB.Close

Set oRange = Nothing
Set oWB = Nothing
Set oXL = Nothing

End Sub

Rene'

 

[fumei]

You can do the same thing in Word. Have a document with the "source" dropdown. Whenever you have another document that wants to use that dropdown, open the source doc, grab the list from the source dropdown, clear the list in the other doc, and refresh with the source list.

Essentially this is the same as the Excel route. I am not sure which would be faster. You would not have to create an instance of Excel, but...does that make a noticeable difference? I don't know. Haven't done a serious comparision.

Gerry

 

[Vittles]

I am assuming that I would make a macro with the script listed above and then have that macro run upon 'entry' of the dropdown field, right?

I am just beginning to experiment with vba & macros - haven't touched the active X controls much yet.

Thanks for the ideas - I was hoping this was a possibility.

 

[rdeleon]

I am assuming you are using Word 97/2000.

In your document go to Tools/Macros/Visual Basic Editor. The VBA editor should open with an explorer type window on the left hand side. Look for your document (should be listed as Project(YourDocName.doc). Expand folders until you see an item called "ThisDocument". Double click and a window should open. This is the window where VBA code goes for the this document. Enter the script in the window with modifications for your excel spreadsheet name, etc.

Since you are new to vba/macros (we were all there once!) I would suggest you search the net for a "VBA 101" overview. If you can get a grasp on the concept of objects, events, methods, and properties, it will start making more sense.

In a nut shell, what the code does is keys in on an event. Specifically the document "Open" event. So to answer your question, the code does not run on entry of the combo box, but when the document is opened.

Rene'

 

[fumei]

I just did a post of a few Word formfield routines that may help. While I can not think of a way to do the update "automatically", there certainly is a way to do the update programatically. That is, run a macro that will do the update to all the documents in a folder.

Gerry

 

[Vittles]

Yes, I am using Word 2000.

I haven't played with comboboxes yet, but may need to look into those further if that is what is required versus doing dropdown formfields. Keep thinking that there would be a way to do this using the on entry macro area to pull a value list.

How would you do the whole folder update? I have several separate groups of templates (in separate folders), each with their own specific list of names. It might be a solution to just update the folders at various times.

I will also have to look for a vba for dummies book as it sounds like this is definitely an area I need to play with a bit. I really appreciate the feedback.

 

[fumei]

Take a look at my thread thread68-871395

Not a single person seems to have looked at it. Oh well. I am gonna write a FAQ. The last bit of code there is an example of looping through a folder getting information from each file.

I also suggest you check out VBAXpress. If you join you can have access to some code. I have a piece there on Conditional listing of drop down boxes. Essentially it takes the condition of one drop down, and uses that to populate another one. This could be applied in a number of ways - from an entrance macro in a formfield to populate others, logic conditions from text user inputs. All sorts of ways.

Gerry

 

[TheBitDoctor]

Vittles:

This code will pull data from an Excel file and populate a ComboBox in a Word doc.

First you'll need to copy this data to a test file: "C:\Data\States.xls"

State	Value	Year
ALABAMA 	287.14	2006
ALASKA	281.95	2000
ARIZONA 	301.76	2000
ARKANSAS	809.75	2006
CALIFORNIA	813.27	2004
COLORADO	402.01	2004
CONNECTICUT	365.34	2008
DELAWARE	272.93	2007
FLORIDA	693.68	2000
GEORGIA	867.10	2002
HAWAII	79.34	2009
IDAHO	429.08	2003
ILLINOIS	215.56	2002
INDIANA	323.92	2007
IOWA	438.63	2000
KANSAS 	88.87	2002
KENTUCKY	66.10	2009
LOUISIANA 	419.21	2009
MAINE 	340.28	2009
MARYLAND 	124.22	2001
MASSACHUSETTS 	191.98	2009
MICHIGAN	652.25	2009
MINNESOTA	8.41	2008
MISSISSIPPI	880.24	2009
MISSOURI	78.85	2003
MONTANA	635.98	2004
NEBRASKA	208.05	2003
NEVADA	553.68	2003
NEW HAMPSHIRE	961.17	2003
NEW JERSEY	889.24	2003
NEW MEXICO	495.81	2007
NEW YORK	589.30	2003
NORTH CAROLINA	814.15	2002
NORTH DAKOTA	981.20	2004
OHIO	663.03	2001
OKLAHOMA	834.67	2004
OREGON	518.35	2008
PENNSYLVANIA	936.58	2007
RHODE ISLAND	385.25	2003
SOUTH CAROLINA	813.53	2004
SOUTH DAKOTA	975.20	2007
TENNESSEE	686.90	2002
TEXAS	297.63	2003
UTAH	321.73	2005
VERMONT	9.32	2008
VIRGINIA	748.35	2002
WASHINGTON	802.50	2006
WEST VIRGINIA	568.65	2008
WISCONSIN	2.79	2002
WYOMING	679.57	2003

NOTE: When you paste this into excel use the 'Data|Text to Columns' function with ASCII character 160 as a delimiter. (Just copy one of the characters from the list.)

You'll need to rename the worksheet to "StateData" or change the code.

Then add a ComboBox to a word document. Set the width to 150, set the ColumnCount property to 3 and the ColumnWidths property to 150;50;50 and set the ListWidth Property to 250.

Then paste this code into a module in the word document.

Public Sub PopulateList()
    Dim cbo As MSForms.ComboBox
    Dim conn As ADODB.Connection, rs As ADODB.Recordset
    Dim sSourceFile As String, sSQL As String
    Dim aStates() As String, lCounter As Long, lNumRecs As Long
    Dim Index As Integer
    
    sSourceFile = "C:\Data\States.xls"

    sSQL = "SELECT `StateData$`.State, `StateData$`.Value, `StateData$`.Year" & _
            " FROM `C:\Data\States.xls`.`StateData$` `StateData$`" & _
            " WHERE (`StateData$`.Value>25)" & _
            " ORDER BY `StateData$`.State"
    Set conn = New ADODB.Connection
    With conn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Properties("Extended Properties") = "Excel 8.0"
    End With
    
    Set rs = New ADODB.Recordset
    Set cbo = ThisDocument.ComboBox1
    
    'First we'll pull the data from the file into a Recordset
    conn.Open sSourceFile
    rs.Open Source:=sSQL, ActiveConnection:=conn, _
        CursorType:=adOpenKeyset, LockType:=adLockOptimistic
    rs.MoveFirst
    
    'Next we need to transfer the data to an array in order to move
    ' it to the ComboBox later
    lNumRecs = rs.RecordCount
    ReDim aStates(lNumRecs - 1, 3)
    Do While Not rs.EOF
        For Index = 0 To 2
            If IsNull(rs.Fields(Index).Value) Then
                aStates(lCounter, Index) = ""
            Else
                aStates(lCounter, Index) = rs.Fields(Index).Value
            End If
        Next Index
        lCounter = lCounter + 1
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing
    'Now we'll use the array to populate the ComboBox List
    cbo.List() = aStates
End Sub

Call this function whenever you want to update the dropdown list. (Like from the OPEN event.)

Ron