Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ComboBox (Word xp)

    I have made some userforms in which there are comboboxes populated with names. Let

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: ComboBox (Word xp)

    Is this for a single user, or in a networked multi-user environment? If multi-user, do you want the lists to be user-specific, or do you want all users to share a common set of lists?

  4. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: ComboBox (Word xp)

    Opening and reading settings (.ini), plain text, delimited text (.csv), and database files is actually not hard. The hard part is (1)

  5. #4
    New Lounger
    Join Date
    Jun 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ComboBox (Word xp)

    It is for multiuser on network, but it is not supposed to be user specific. It's just a list over employees, and the purpose is to put the right name in a formfield in some letters. If somebody quits, or gets employed, I would like anybody, even those that don

  6. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: ComboBox (Word xp)

    A very simple solution that will work well if the data don't have to be modified frequently is to store the list in a text file in a shared network folder. You can use a FileSystemObject to manipulate a standard text file, or use PrivateProfileString to manipulate a text file structured like a Windows 3.x INI file. Look up FileSystemObject and PrivateProfileString in the online help for Word VBA.

    For multi-user, it's best to put the list as a table in a small Access database. The end users don't need to have Access installed for this, you can use DAO or ADO to retrieve data from the table.

  7. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: ComboBox (Word xp)

    As a footnote to Hans' message, ADO also can read Excel sheets, so if you do not have someone who can maintain an Access database, Excel also should work. Naturally the sheet should have just a very plain and simple "data table" layout without any formatting (no blank lines between data rows, hard page breaks, etc.).

    For reference, here is how you connect to an Excel sheet named F:emplist.xls using ADO:

    1. <LI>Set a Reference to Microsoft ActiveX Data Objects 2.x Library (the version will vary, but it should correspond to msado15.dll on the Location line at the bottom of the dialog).
      <LI>Use code such as the following, which uses the ODBC driver for Excel under the covers:
      <pre>Dim cn As New ADODB.Connection
      cn.Open "PROVIDER=MSDASQL;" & _
      "DRIVER={Microsoft Excel Driver (*.xls)};" & _
      "DBQ=F:emplist.xls"</pre>

      <LI>You then can simply open the front sheet of emplist.xls as a table into an ADO Recordset:
      <pre>Dim rs As New ADODB.Recordset
      rs.Open "[Sheet1$]", cn, adOpenDynamic, , adCmdTable</pre>
    Please note that about half of that came from a book, untested, straight to you. Hope this helps.

  8. #7
    New Lounger
    Join Date
    Jun 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ComboBox (Word xp)

    That sounds great, because I can make a small database in Access that will be very easy to maintain. Let's say I make one called "Anst

  9. #8
    New Lounger
    Join Date
    Jun 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ComboBox (Word xp)

    Seems easy, thank you!

  10. #9
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: ComboBox (Word xp)

    This is a code snippet to populate a listbox after querying an Access MDB database. Your query would be simpler, but here's a general idea of the flow of it. Note that the variables in bold are set before this code is called, and the "help text" is a multiline text box styled as flat with the form that prompts the user about search results and options.
    <pre>' Create an ADO recordset by querying the file
    ' Early binding - for development
    Dim cnC As New ADODB.Connection, rsC As New ADODB.Recordset
    ' Late binding - for deployment
    'Dim cnC As Object, rsC As Object
    'Set cnC = CreateObject("ADODB.Connection")
    'Set rsC = CreateObject("ADODB.Recordset")
    With cnC
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Open "Data Source=" & strValDBpath
    End With
    Dim strSELECT As String
    strSELECT = "SELECT Client.CliNickName, Client.CliCode, Client.CliSysNbr " & _
    "FROM Client " & _
    "WHERE (" & strTarget & " Like '" & strQuery & "') " & _
    "ORDER BY Client.CliNickName"
    With rsC
    .CacheSize = 20
    .Open strSELECT, cnC, 3, 3, 1 'adOpenStatic, adLockOptimistic, adCmdText
    End With

    If Not rsC.EOF Then
    Me.lblQueryResults2.Caption = "Query Results (Clients):"
    ' Populate list box and select first item
    With Me.lbxCliMat
    While Not rsC.EOF
    .AddItem rsC.Fields("CliNickname").Value & _
    " {" & rsC.Fields("CliCode").Value & "|" & _
    rsC.Fields("CliSysNbr").Value & "}"
    rsC.MoveNext
    Wend
    .SetFocus
    .ListIndex = 0
    .Selected(0) = True
    End With
    ' Reset buttons for convenience
    Me.btnFind.Default = False
    With Me.btnLookupMatter2
    .Enabled = True
    .Default = True
    End With
    ' Set help text
    Me.txtHelp2.Value = "Select the client and click Look up Matter or, " & _
    "if the client is not listed, try searching again or use the " & _
    "New Matter button."
    Else
    ' Reset buttons for convenience
    Me.btnLookupMatter2.Enabled = False
    ' Set help text
    If Me.optPrefix.Value = True Then
    Me.txtHelp2.Value = "No matches for your client name query for '" & _
    Trim(Me.txtQuery.Value) & "...'. Please try again."
    ElseIf Me.optCode.Value = True Then
    Me.txtHelp2.Value = "No matches for your client code query for '" & _
    Trim(Me.txtQuery.Value) & "'. Please try again."
    Else
    Me.txtHelp2.Value = "No matches for your client name query for '..." & _
    Trim(Me.txtQuery.Value) & "...'. Please try again."
    End If
    End If

    ' Clean up ADO objects
    rsC.Close
    Set rsC = Nothing
    cnC.Close
    Set cnC = Nothing</pre>

    Hope this helps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •