Array Question

[LEU]

Can you add a hard return in an array? So when picked it would look like this
in my document:

This is a test
How does it look

Instead of this:

This is a test How does it look

My macro is as follows:

Dim myArray
myArray = Split("This is a test How does it look", "|")
With Cmb1
.List = myArray
.ListIndex = 0
.MatchRequired = True
End With

LEU

 

[Jonathan West]

Can you add a hard return in an array? So when picked it would look like
this
in my document:

This is a test
How does it look

Instead of this:

This is a test How does it look

I'm not clear what you are trying to achieve. Are you trying to get lines of
text separated by hard returns into an array, or are you trying to place the
elements of an array into a document separated by returns?

 

[LEU]

Hi Jonathan,

I trying to place the elements of an array into a document separated by
returns.

LEU

 

[Jonathan West]

Hi Jonathan,

I trying to place the elements of an array into a document separated by
returns.

Selection.InsertAfter Join(myArray, vbCrLf)

The line above will insert the entire contents of the array into successive
paragraphs at the selection point

 

[LEU]

I do not understand where to put this line. I might not be explaining myself
well here either. I have a document with bookmarks that when I open my form
to populate my document one of the fields is a combobox with a list of
choices to insert. What I was trying to do is some of the choices when picked
are single lines of information that would be save to my bookmark called
‘bkprotitle’ and others when picked and saved to the same bookmark would have
two lines of information.

LEU

 

[Doug Robbins - Word MVP]

I would suggest that you should use a multicolumn combobox and then you use
the BoundColumn property of the combobox to retrieve the information from
each column, and using an If...EndIf construction to test if there is any
data in the second column and if there is, append a CR and that data into
the information that you insert into the bookmark.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[LEU]

Jonathan & Doug,

You are talking over my head. I am not sure how to set my form like you
said. I opened my form and in my combobox ‘Cmb2’ I changed the ColumnCount to
2. The BoundColumn is set to 1. For an example of what I would like to do is
in column 1 I want Test 1, Test 2 and Test 3 and in column 2 I want a blank
line and Plant 2. My macros right now are as follows:

Private Sub UserForm_Initialize()
Dim myArray
Set oFF = ActiveDocument.FormFields
myArray = Split("Test 1|Test 2|Test 3", "|")
With Cmb2
.List = myArray
.ListIndex = 0
.MatchRequired = True
End With
Set oBMs = ActiveDocument.Bookmarks
Cmb2.Value = oBMs("bkProTitle").Range.Text
End Sub


Private Sub cmdOK_Click()
FillBookmark "bkProTitle", Cmb2.Value
Me.Hide
End Sub

So how would I change my macros so if I picked Test 1 from column 1 and
nothing from column 2 that in my document title it would say Test 1 only. Or
if I would pick Test 2 from column 1 and Plant 2 from column 2 that in my
document title it would say Test 2 in the first line and Plant 2 in the next
line.


LEU

 

[Doug Robbins - Word MVP]

The following shows how to load a listbox, or a combobox with information
that you store in a separate Word document. It also shows how you access
the data in each column of the listbox or combobox.

This routine loads a listbox with client details stored in a table in a
separate
document (which makes it easy to maintain with additions, deletions etc.),
that document being saved as Clients.Doc for the following code.

On the UserForm, have a list box (ListBox1) and a Command Button
(CommandButton1) and use the following code in the UserForm_Initialize() and
the CommandButton1_Click() routines

Private Sub UserForm_Initialize()
Dim sourcedoc As Document, i As Integer, j As Integer, myitem As Range,
m As Long, n As Long
' Modify the path in the following line so that it matches where you
saved Clients.doc
Application.ScreenUpdating = False
' Open the file containing the client details
Set sourcedoc = Documents.Open(FileName:="e:\worddocs\Clients.doc")
' Get the number or clients = number of rows in the table of client
details less one
i = sourcedoc.Tables(1).Rows.Count - 1
' Get the number of columns in the table of client details
j = sourcedoc.Tables(1).Columns.Count
' Set the number of columns in the Listbox to match
' the number of columns in the table of client details
ListBox1.ColumnCount = j
' Define an array to be loaded with the client data
Dim MyArray() As Variant
'Load client data into MyArray
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 ListBox1
ListBox1.List() = MyArray
' Close the file containing the client details
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
End Sub

Private Sub CommandButton1_Click()
Dim i As Integer, Addressee As String
Addressee = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
Addressee = Addressee & ListBox1.Value & vbCr
Next i
ActiveDocument.Bookmarks("Addressee").Range.InsertAfter Addressee
UserForm2.Hide
End Sub



--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[LEU]

Doug,

Thank you for all your help. I did what you said and it worked. One thing is
it puts in a extra hard return if in the client doc column 2 is blank. Is
there a way to stop this from happening?

LEU

 

[Doug Robbins - Word MVP]

It should be possible to prevent it being inserted. Show us the code that
you ended up with so we can see exactly what you are doing.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[LEU]

Here is my code:

Option Explicit
Private oBMs As Bookmarks


Private Sub UserForm_Initialize()
Dim myType
Set oFF = ActiveDocument.FormFields
myType = Split("CONTINUOUS|INFORMATION|REFERENCE", "|")
With Cmb1
.List = myType
.ListIndex = 0
.MatchRequired = True
End With

Dim sourcedoc As Document, i As Integer, j As Integer, myitem As Range, m As
Long, n As Long
' Modify the path in the following line so that it matches where you
Saved Clients.Doc
Application.ScreenUpdating = False
' Open the file containing the client details
Set sourcedoc = Documents.Open(FileName:="u:\Procedure Master
Template\Clients.doc")
' Get the number or clients = number of rows in the table of client
details less one
i = sourcedoc.Tables(1).Rows.Count - 1
' Get the number of columns in the table of client details
j = sourcedoc.Tables(1).Columns.Count
' Set the number of columns in the Listbox to match
' the number of columns in the table of client details
ListBox1.ColumnCount = j
' Define an array to be loaded with the client data
Dim myArray() As Variant
'Load client data into MyArray
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 ListBox1
ListBox1.List() = myArray
' Close the file containing the client details
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges

On Error Resume Next
Set oFF = ActiveDocument.FormFields
Set oBMs = ActiveDocument.Bookmarks
Text1.Value = oBMs("bknbr").Range.Text
Text2.Value = oBMs("bkRevision").Range.Text
Text3.Value = oBMs("bktitle").Range.Text
Cmb1.Value = oBMs("bkUse").Range.Text
Text4.Value = oBMs("bkDIC").Range.Text
Text5.Value = oBMs("bkPCN").Range.Text
Text6.Value = oBMs("bkQPR").Range.Text
Text7.Value = oBMs("bkExt1").Range.Text
Text8.Value = oBMs("bkSponsor").Range.Text
Text9.Value = oBMs("bkExt2").Range.Text
Text10.Value = oBMs("bkDate").Range.Text
End Sub

and

Private Sub cmdOK_Click()
FillBookmark "bknbr", Text1.Text
FillBookmark "bknbr2", Text1.Text
FillBookmark "bknbr3", Text1.Text
FillBookmark "bknbr4", Text1.Text
FillBookmark "bkRev", Text2.Text
FillBookmark "bkRevision", Text2.Text
FillBookmark "bktitle", Text3.Text
FillBookmark "bktitle1", Text3.Text
FillBookmark "bkuse", Cmb1.Value
FillBookmark "bkuse1", Cmb1.Value
FillBookmark "bkDic", Text4.Text
FillBookmark "bkPCN", Text5.Text
FillBookmark "bkQPR", Text6.Text
FillBookmark "bkExt1", Text7.Text
FillBookmark "bkSponsor", Text8.Text
FillBookmark "bkExt2", Text9.Text
FillBookmark "bkDate", Text10.Text

Dim i As Integer, Addressee As String
Addressee = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
Addressee = Addressee & ListBox1.Value & vbCr
Next i
FillBookmark "bkProTitle", Addressee
Me.Hide
End Sub


LEU

 

[Doug Robbins - Word MVP]

Modify this part of your code

Dim i As Integer, Addressee As String
Addressee = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
Addressee = Addressee & ListBox1.Value & vbCr
Next i
FillBookmark "bkProTitle", Addressee
Me.Hide

To

Dim i As Integer, Addressee As String
Addressee = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
If ListBox1.Value <> "" then
Addressee = Addressee & ListBox1.Value & vbCr
End If
Next i
FillBookmark "bkProTitle", Addressee
Me.Hide


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP