Array Question

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
Thu Aug 02 13:03:49 CDT 2007


"LEU" <LEU@discussions.microsoft.com> wrote in message
news:4F0C392D-AB4D-4321-BB40-A2B83808FDBC@microsoft.com...
> 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?


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup

LEU
Thu Aug 02 13:30:03 CDT 2007

Hi Jonathan,

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

LEU



"Jonathan West" wrote:

>
> "LEU" <LEU@discussions.microsoft.com> wrote in message
> news:4F0C392D-AB4D-4321-BB40-A2B83808FDBC@microsoft.com...
> > 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?
>
>
> --
> Regards
> Jonathan West - Word MVP
> www.intelligentdocuments.co.uk
> Please reply to the newsgroup
>
>
>

Jonathan
Thu Aug 02 15:58:33 CDT 2007


"LEU" <LEU@discussions.microsoft.com> wrote in message
news:BAE2FD64-066D-4B8F-9D01-BD3E45AD0EC2@microsoft.com...
> 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


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup

LEU
Thu Aug 02 17:20:01 CDT 2007

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


"Jonathan West" wrote:

>
> "LEU" <LEU@discussions.microsoft.com> wrote in message
> news:BAE2FD64-066D-4B8F-9D01-BD3E45AD0EC2@microsoft.com...
> > 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
>
>
> --
> Regards
> Jonathan West - Word MVP
> www.intelligentdocuments.co.uk
> Please reply to the newsgroup
>
>

Doug
Thu Aug 02 21:32:05 CDT 2007

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" <LEU@discussions.microsoft.com> wrote in message
news:A1D0CDFE-43FB-4FDE-9D3C-4051030EC746@microsoft.com...
>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
>
>
> "Jonathan West" wrote:
>
>>
>> "LEU" <LEU@discussions.microsoft.com> wrote in message
>> news:BAE2FD64-066D-4B8F-9D01-BD3E45AD0EC2@microsoft.com...
>> > 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
>>
>>
>> --
>> Regards
>> Jonathan West - Word MVP
>> www.intelligentdocuments.co.uk
>> Please reply to the newsgroup
>>
>>

LEU
Fri Aug 03 14:36:02 CDT 2007

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" wrote:

> 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
>

Doug
Fri Aug 03 16:00:10 CDT 2007

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" <LEU@discussions.microsoft.com> wrote in message
news:DA75356B-96B9-499A-BB73-C75BFF1AECC4@microsoft.com...
> 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" wrote:
>
>> 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
Fri Aug 03 17:08:27 CDT 2007

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" wrote:

> 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" <LEU@discussions.microsoft.com> wrote in message
> news:DA75356B-96B9-499A-BB73-C75BFF1AECC4@microsoft.com...
> > 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" wrote:
> >
> >> 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
> >>
> >
>
>
>

Doug
Sat Aug 04 05:10:22 CDT 2007

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" <LEU@discussions.microsoft.com> wrote in message
news:ACD67AB7-0646-437B-A4C0-44E41D57A9A0@microsoft.com...
> 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" wrote:
>
>> 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" <LEU@discussions.microsoft.com> wrote in message
>> news:DA75356B-96B9-499A-BB73-C75BFF1AECC4@microsoft.com...
>> > 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" wrote:
>> >
>> >> 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
Sat Aug 04 09:22:07 CDT 2007

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" wrote:

> 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" <LEU@discussions.microsoft.com> wrote in message
> news:ACD67AB7-0646-437B-A4C0-44E41D57A9A0@microsoft.com...
> > 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" wrote:
> >
> >> 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" <LEU@discussions.microsoft.com> wrote in message
> >> news:DA75356B-96B9-499A-BB73-C75BFF1AECC4@microsoft.com...
> >> > 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" wrote:
> >> >
> >> >> 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
> >> >>
> >> >
> >>
> >>
> >>
>
>
>

Doug
Sun Aug 05 00:01:06 CDT 2007

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

"LEU" <LEU@discussions.microsoft.com> wrote in message
news:B4154487-2E89-4D2E-B567-7B816B33F0BE@microsoft.com...
> 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" wrote:
>
>> 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" <LEU@discussions.microsoft.com> wrote in message
>> news:ACD67AB7-0646-437B-A4C0-44E41D57A9A0@microsoft.com...
>> > 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" wrote:
>> >
>> >> 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" <LEU@discussions.microsoft.com> wrote in message
>> >> news:DA75356B-96B9-499A-BB73-C75BFF1AECC4@microsoft.com...
>> >> > 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" wrote:
>> >> >
>> >> >> 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
>> >> >>
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>