Word userforms populating text boxes using excel data

[Mr Ronsen]

Hi, i have been combing the internet for about 2 weeks now looking for a solution to my problem

A warning beforehand, I am using office 2003 I have a template in word with various text boxes but i only need to use 6 of these which i have bookmarked ("bookmark1" - 6). When the template opens a UserForm with a Listbox ("Listbox1") appears. The list box has the names and information from a spreadsheet that looks like this (these names are on column 1 of the spreadsheet)

Name	Start date	Annual Leave Remaining	Annual Leave entitlement
Dave	09/01/10	12	32
John	08/02/09	3	34
Bobby C	07/03/08	9	32

When they choose a name and click an "OK" command button i would like the textboxes on the form to autofill with information from the UserForm.
for example if "Dave" was selected, text box 1 on the form would fill in the name, box 2 the start date, box 3 the annual leave remaining etc, just for Dave.

I have everything in place i just dont know the code to get it from the userform to fill in the boxes if you could help at all it would mean a lot and bring an end to my epic search for a solution

Mr Ronsen

 

[Kenneth Hobson]

Welcome to the forum! You might be better served in an MSWord forum. Normally, we are dealing with automating MSWord from Excel in this forum if we work with the two applications. Of course knowing one way is similar to the other. You just have to reference objects differently. Without code or an example, it is hard to help. Shared sites like box.net can be used to share files for this forum's problem solvers to help you and you them.

Some of these may give you an idea. I am not sure that bookmark is what you have but try those threads first.

Option Explicit

'Automate MSWord from another VBA Application like Excel
' Automate Microsoft Word from Excel, using VBA

'TypeText method
' Populate Word Document from Excel
' Populate Word Document from Excel
' Solved: Customizing How Word Displays Information from Excel - Page 2
' Alternative To ActiveCell.Row
' Excel to Word- paragraph and page setup

'Copy from Excel, paste to Word
'Lucas, Solved: Moving data to a Word document

'FormFields
' export cells from excel to MS word form document
' export cells from excel to MS word form document
' Import text fields from Word
' Content Controls
' Exporting data from content control fields in word to excel 2010

'Add Hyperlink to Bookmark
' Use Excel VBA to add a hyperlink to a word document
'Steiner, VBA Express : Word - Insert Text into Bookmarks
'Colin_L, excel to word

'Save OLEObject as MSWord Document
' SaveAs with embedded Word document - OLEObjects
' opening/extracting ole object file in excel via excel VBA
' Excel Export to OLE Object with user prompted SaveAs HELP

'Add Table to MSWord
' Solved: Building Word Documents based on Individual Excel Rows
' Transfering Data To Word
' [SOLVED] Macro to copy multiple tables in a single sheet from excel to word doc

'Import Word Tables
'vog, Importing Word document data in Excel
'Ruddles, VB Code to Copy MS Word Tables Into Excel

'snb, Word Tables
' Solved: copy from excel to word unmerged table
' Excel Data Matching Word Tables & Looping


'Get Optionbutton info from MSWord DOC
' Solved: Get MS Word OptionButton value from Excel VBA

'FindReplace Text
' Replace word in MS Word with varable from MS Excel
' Problem with my search/replace macro
' Problem with my search/replace macro
' Solved: Search document for words in keyword list
' WD2000: VBA: How to Count the Occurrences of a Word or Phrase
' Generating a Count of Word Occurrences (Microsoft Word)

' Find/Replace Macro to Word

'Bookmarks
' Solved: Exporting data to Word
'Colin_L, excel to word
' Solved: add additional data to word document's bookmark in excel

'Mail Merge
' Mail Merge from Excel?
' Print mail merge document
'Word 's Catalogue/Directory Mailmerge facility (the terminology depends on the Word version). _
To see how to group records with any mailmerge data source supported by Word, _
check out my Microsoft Word Catalogue/Directory Mailmerge Tutorial at:
' http://lounge.windowssecrets.com/index.php?showtopic=731107
' or
' http://www.gmayor.com/Zips/Catalogue Mailmerge.zip
' Mail Merge from Excel to MSWord: ElephantsRus
' https://app.box.com/s/0zlydxinl10t23mifkrr

' Control Word from Excel
' Control Word from Excel
' Speeding up automation

 

[Macropod]

Hi Mr Ronsen,

Whilst it's possible to populate your dropdown with the Excel data, are you aware that dropdown formfields are limited to 25 entries? If you have more than that, you'll need to employ a userform instead.

 

[Mr Ronsen]

Hi,

Thanks for getting back to me so fast. I have the userform in place and it has the information from excel in the listbox. I just dont know how to get the information from the userform to the bookmarked text boxes on the word document. It has to be all the information specific to that person, so everything in that row.

The information on the excel sheet will change as well, the userform takes this into account and updates when the spreadsheet does. But for this reason i cant simply special paste the information into there. Plus it will be used buy about 13 different people on 13 different computers so the userform really is the best way to do it.

I hope that makes sense

At the minute i have no code for transfering the information from the userform to the document. i have the code for the userform to get the informaation form the spreadsheet which looks like this:

Sub AutoNew()
Dim myForm As UserForm1
Dim xlApp As Object
Dim xlbook As Object
Dim Listarray As Variant
Dim bStartApp As Boolean
On Error Resume Next
Set myForm = New UserForm1
Set xlApp = GetObject(, "Excel.Application")
  If Err Then
    bStartApp = True
    Set xlApp = CreateObject("Excel.Application")
  End If
  On Error GoTo 0
  With xlApp
    Set xlbook = .Workbooks.Open("G:\office\Consultant database.xls")
    Listarray = xlbook.Sheets(1).Range("A1").CurrentRegion.Value
    xlbook.Close SaveChanges:=False
    Set xlbook = Nothing
  End With
  If bStartApp Then xlApp.Quit
  Set xlApp = Nothing
  With myForm.ListBox1
    .ColumnCount = UBound(Listarray, 2)
    .ColumnWidths = "145;0;0;0;0;0;0;0;0" 'Set the widths of the columns so that the data in the first column only is visible
    .Clear
    .List() = Listarray
    .RemoveItem (0) 'Remove the column headings
  End With
myForm.Show
Unload myForm
Set myForm = Nothing
End Sub

I will post it on box.net as well so i can share the files im working with

Thank you for your help

Mr Ronsen

 

[Macropod]

Here's some code to both populate the userform and the first table in the document. Four columns are assumed for both the userform data and the table. Rows are added to the table as needed:

Private Sub UserForm_Initialize()
'Late binding. No reference to Excel Object required.
Dim xlApp As Object, xlWB As Object, xlWS As Object
Dim lRow As Long, i As Long
Set xlApp = CreateObject("Excel.Application")
'Get the Excel data.
Set xlWB = xlApp.Workbooks.Open("G:\Office\Consultant database.xls", False, True, , , , , , , , , , False)
Set xlWS = xlWB.Worksheets(1)
lRow = xlWS.Cells.SpecialCells(11).Row '11= xlCellTypeLastCell
With Me.ListBox1
  .ColumnCount = 1
  'Populate the listbox.
  For i = 2 To lRow
    'Use .AddItem property to add a new row for each record
    .AddItem xlWS.Range("A" & i).Value
    .Column(1, i - 2) = xlWS.Range("B" & i).Value
    .Column(2, i - 2) = xlWS.Range("C" & i).Value
    .Column(3, i - 2) = xlWS.Range("D" & i).Value
  Next i
End With
'Clean up
xlWB.Close False
xlApp.Quit
Set xlWS = Nothing: Set xlWB = Nothing: Set xlApp = Nothing
End Sub

Private Sub CommandButton1_Click()
Dim i As Long
With ActiveDocument.Tables(1)
  For i = 2 To .Rows.Count + 1
    With .Rows(i)
      'check that the row is empty, then update
      If Len(.Cells(1).Range.Text) = 2 Then
        .Cells(1).Range.Text = Me.ListBox1.Value
        .Cells(2).Range.Text = Me.ListBox1.Column(1)
        .Cells(3).Range.Text = Me.ListBox1.Column(2)
        .Cells(4).Range.Text = Me.ListBox1.Column(3)
        Exit For
      End If
    End With
    'add a row if we need one
    If i = .Rows.Count Then .Rows.Add
  Next
End With
Application.ScreenRefresh
End Sub

I had put this together before seeing that you had code to populate the userform. However, the code to populate the document works regardless. You'll not some differences too in the way the data are sourced and the form is populated; there's stuff there you might find useful.