Re: Using a listbox in a form part 2

• Previous message: Peter Hewett: "Re: using checkboxes- Peter Hewett"
• In reply to: Perry: "Re: Using a listbox in a form part 2"
• Next in thread: Doug Robbins - Word MVP - DELETE UPPERCASE CHARACTERS FROM EMAIL ADDRESS: "Re: Using a listbox in a form part 2"
• Reply: Doug Robbins - Word MVP - DELETE UPPERCASE CHARACTERS FROM EMAIL ADDRESS: "Re: Using a listbox in a form part 2"
• Messages sorted by: [ date ] [ thread ]

Date: Sun, 22 Feb 2004 16:24:30 -0800

I tired several itereations with numerous combinations of
coding to see if I could get your suggestions to work. The
bookmarks now load, but the array won't load into the listbox
so all the fields are blank. Could you take another look and
see if you can help me make some sense of this?
>-----Original Message-----
>Couple of remarks:
>
>- I wouldn't use the Boundcolumn property of listbox to
counter the value.
>Instead, use the Column or List property, like in below
pseudo statements
>when you iterate through the ListItems:
>
> Debug.Print Me.ListBox1.Column([ColumnIndex],
[RowIndex])
> Debug.Print Me.ListBox1.List([RowIndex],
[ColumnIndex])
>
>Example of above statements:
>
> 'get the # of rows (0 based); x = rowindex
> For X = 0 To Me.ListBox1.ListCount - 1
> 'get the # of columns, (0 based); y = ColumnIndex
> For Y = 0 To Me.ListBox1.ColumnCount - 1
>
> Debug.Print Me.ListBox1.List(X, Y)
> Debug.Print Me.ListBox1.Column(Y, X)
>
> Next Y
> Next X
>
>To let the columns correspond with the FormFields in the
document, see below
>example:
>
>Name in Column 1, Addres in Column 2, Zipcode in
Column3, Residence in
>Column 4
>
>With ActiveDocument.FormFields
> 'get the # of rows (0 based); x = rowindex
> For X = 0 To Me.ListBox1.ListCount - 1
>
> .Item("BrokerName").Result = Me.ListBox1.List(X, 0)
> .Item("BrokerAdd").Result = Me.ListBox1.List(X, 1)
> .Item("BrokerZip").Result = Me.ListBox1.List(X, 2)
> .Item("BrokerCity").Result = Me.ListBox1.List(X, 3)
>
> Next X
>End With
>
>Krgrds,
>Perry
>
>"BruDe" <anonymous@discussions.microsoft.com>
schreef in bericht
>news:136ee01c3f846$36dda380$a101280a@phx.gbl...
>> I've been trying to use an Array to enter data from a
>> table into several form fields in a protected word
>> document. However, the code I'm using won't allow me
(at
>> this point) to use the different Bookmarks and instead
>> will only insert the data (a company name and address)
>> into a single bookmark. Also the format used causes
each
>> cell of the data to be entered into its own paragraph line
>> so an address that should look like this:
>>
>> XYZ, Inc
>> 1234 Any St
>> Nocity, ST 00000
>>
>> Looks like this instead:
>>
>> XYZ, Inc
>> 1234 Any St
>> Nocity
>> ST
>> 00000
>>
>> I have the document set up with fields for each part of
>> the address (Compname, Add, City, State, Zip) and a
second
>> form field where I want to repeat the company name only
in
>> the body of the letter.
>>
>> The code I am using thus far is as follows:
>>
>> Private Sub UserForm_Initialize()
>>
>> Dim BName As FormField
>> Dim BAdd As FormField
>> Dim BCity As FormField
>> Dim BState As FormField
>> Dim BZip As FormField
>> Dim BPhone As FormField
>> Dim BName2 As FormField
>>
>>
>> ' If no documents are open or if no form fields
>> ' exist in the active document, or for other errors
>> ' exit this routine
>>
>> ' On Error GoTo errhandler
>>
>> ' Enter the name of the person signing the letter
>> ' from the available list
>>
>> ' Set BName = ActiveDocument.FormFields
("BrokerName")
>> ' Set BAdd = ActiveDocument.FormFields("BrokerAdd")
>> ' Set BCity = ActiveDocument.FormFields("BrokerCity")
>> ' Set BState = ActiveDocument.FormFields
("BrokerState")
>> ' Set BZip = ActiveDocument.FormFields("BrokerZip")
>> ' Set BPhone = ActiveDocument.FormFields
("BrokerPhone")
>> ' Set BName2 = ActiveDocument.FormFields
("BrokerName2")
>>
>> Dim sourcedoc As Document, i As Integer, j As
Integer,
>> myitem As Range, m As Long, n As Long
>> ' the following is to set the path to the the BrokerInfo
>> Document containing the list of Brokers and their
addresses
>> Application.ScreenUpdating = False
>> ' open the source document contaning the Broker
>> informatiion
>> Set sourcedoc = Documents.Open
>>
(FileName:="L:\ColumbiaBank\Projects\AdverseActLetters\C
onv
>> erted Templates\BrokerInfo.doc")
>> ' Set the number of Rows in the Broker info table, less 1
>> (the header row)
>> i = sourcedoc.Tables(1).Rows.Count - 1
>> ' Get the number of Brokers in the table
>> j = sourcedoc.Tables(1).Columns.Count
>> ' Set the number of columns to match the number of
columns
>> in the Broker info Table
>> ListBox1.ColumnCount = j
>> ' Define an array to be loaded with Broker information
>> Dim MyArray() As Variant
>> ' Load the data into the list box
>> ReDim MyArray(i, j)
>> For n = 0 To j - 1
>> For m = 0 To i - 1
>> Set myitem = sourcedoc.Tables(1).Cell(m + 2, n
>> + 1).Range
>> myitem.End = myitem.End - 1
>> MyArray(m, n) = myitem.Text
>> Next m
>> Next n
>> ' Load data into BrokerLBx
>> ListBox1.List = MyArray
>> ' Close the file containing the Broker information
>> sourcedoc.Close
SaveChanges:=wdDoNotSaveChanges
>> errhandler:
>> End Sub
>>
>>
>> Private Sub cmdOkButton_Click()
>> Dim i As Integer, BrokerName As String
>> BrokerName = ""
>> For i = 1 To ListBox1.ColumnCount
>> ListBox1.BoundColumn = i
>> BrokerName = BrokerName & ListBox1.Value & vbCr
>> Next i
>> ActiveDocument.FormFields("BrokerName").Result =
>> BrokerName
>> ' ActiveDocument.FormFields("BrokerAdd").Result =
>> BrokerAdd
>> ' ActiveDocument.FormFields("BrokerCity").Result =
>> BrokerCity
>> ' ActiveDocument.FormFields("BrokerZip").Result =
>> BrokerCity
>> ' ActiveDocument.FormFields("BrokerPhone").Result =
>> BrokerPhone
>> ' ActiveDocument.FormFields("BrokerName2").Result =
>> BrokerName
>> UserForm.Hide
>> End Sub
>>
>> Note that I've disabled all the form fields save
>> BrokerName since I haven't been able to figure out how to
>> get the data into the separate fields. Also, the macro I
>> use to display the form gives me a visual basic error
>> message:
>>
>> Runtime Error '4198':
>> Command failed
>>
>> The macro code is as follows:
>>
>> Sub GetList()
>> UserForm.Show
>> End Sub
>>
>> The array itself does work and the listbox is populated
>> with the company name and adressses, but I still need to
>> transfer the selected data to the individual form fields
>> and copy the company name to the second form field.
Any
>> help provided to make this work properly would be greatly
>> appreciated
>>
>
>
>.
>

• Previous message: Peter Hewett: "Re: using checkboxes- Peter Hewett"
• In reply to: Perry: "Re: Using a listbox in a form part 2"
• Next in thread: Doug Robbins - Word MVP - DELETE UPPERCASE CHARACTERS FROM EMAIL ADDRESS: "Re: Using a listbox in a form part 2"
• Reply: Doug Robbins - Word MVP - DELETE UPPERCASE CHARACTERS FROM EMAIL ADDRESS: "Re: Using a listbox in a form part 2"
• Messages sorted by: [ date ] [ thread ]