Choosing name from list box

[Julia]

Hi,

I have a template (for creating letters) that I have set up for my company.
I have all the employees names, direct dial, fax, email, etc. in a table in a
separate document. The letterhead template has been set up as merge file.
There is a form with a list box that the user uses to select their name.
After they select their name, their personal information from the table
should be inserted into the letterhead document.

The following macro works except, no matter which name I select from the
listbox, I get the last name in the list. Can anyone tell me what I'm doing
wrong??

---------------------------------------------
Private Sub Document_New()

Dim oDoc As Document
Dim ofrmLetterhead As frmLetterhead

Set ofrmLetterhead = New frmLetterhead

Set oDoc = ActiveDocument

Load frmLetterhead

frmLetterhead.lstNames.Clear
frmLetterhead.lstNames.SetFocus

DoEvents

oDoc.MailMerge.DataSource.ActiveRecord = wdFirstRecord

Do
frmLetterhead.lstNames.AddItem
oDoc.MailMerge.DataSource.DataFields("Name")
nCurrRecord = oDoc.MailMerge.DataSource.ActiveRecord
oDoc.MailMerge.DataSource.ActiveRecord = wdNextRecord
Loop Until nCurrRecord = oDoc.MailMerge.DataSource.ActiveRecord

frmLetterhead.lstNames.ListIndex = -1


frmLetterhead.Show


If btnOKClicked = True Then

ActiveDocument.Bookmarks("bkName").Range.Text =
oDoc.MailMerge.DataSource.DataFields("Name")
ActiveDocument.Bookmarks("bkDD").Range.Text =
oDoc.MailMerge.DataSource.DataFields("DD")
ActiveDocument.Bookmarks("bkEMail").Range.Text =
oDoc.MailMerge.DataSource.DataFields("Email")
ActiveDocument.Bookmarks("bkFax").Range.Text =
oDoc.MailMerge.DataSource.DataFields("Fax")
ActiveDocument.Bookmarks("bkSig").Range.Text =
oDoc.MailMerge.DataSource.DataFields("Sig")

Else
ActiveDocument.Close wdDoNotSaveChanges

End If

' Destroy the instance of the ofrmLetterhead object
Unload ofrmLetterhead
Set ofrmLetterhead = Nothing

Selection.GoTo What:=wdGoToBookmark, Name:="bkStop"

End With
End Sub

 

[Helmut Weber]

Hi Julia,

I don't know why, but at least for me this helped:
Not only
oDoc.MailMerge.DataSource.ActiveRecord = 1 ' no constant
plus
oDoc.MailMerge.DataSource.FirstRecord = 1
oDoc.MailMerge.DataSource.LastRecord = 1

I don't remember, whether this is zero-based or not.
If so, then use 0 instead of 1.

HTH
Greetings from Bavaria, Germany

Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/

 

[Chuck]

I don't see anything in your code that correlates the listindex of the name
selected from the list box with the corresponding record in your merge data
file. Am I missing something?

Is there a reason you're not loading all the information from the data
source into the list box? You have 10 columns to play with and you're only
using 5 variables so as long as you're loading the names into the list box
you could load the other variables as well - so that when the user selects a
name, you can fill in the bookmarks from the list box without having to refer
back to the mailmerge, eg:

Dim n as Long

n = frmLetterhead.lstNames.ListIndex

With ActiveDocument
.Bookmarks("bkName").Range.Text = frmLetterhead.lstNames.Column(0, n)
.Bookmarks("bkDD").Range.Text = frmLetterhead.lstNames.Column(1, n)
.Bookmarks("bkEmail").Range.Text = frmLetterhead.lstNames.Column(2, n)
.Bookmarks("bkFax").Range.Text = frmLetterhead.lstNames.Column(3, n)
.Bookmarks("bkSig").Range.Text = frmLetterhead.lstNames.Column(4, n)
End With

 

[Cissy]

Hi Helmut, I tried this, it didn't work for me, I'm sure because I am not
putting it in the right place...can you explain? Thanks much

 

[Cissy]

Hi Chuck:

I don't see anything in your code that correlates the listindex of the name
selected from the list box with the corresponding record in your merge data
file. Am I missing something?

I'M SURE I'M MISSING SOMETHING. ANY IDEAS? HOW DO I LINK THE LISTINDEX OF
THE NAME FROM THE LISTBOX WITH THE DATA RECORD?

Is there a reason you're not loading all the information from the data
source into the list box? You have 10 columns to play with and you're only
using 5 variables so as long as you're loading the names into the list box
you could load the other variables as well - so that when the user selects a
name, you can fill in the bookmarks from the list box without having to refer
back to the mailmerge, eg:

NO REASON, JUST TRYING TO PUT THIS TOGETHER WITH MY LIMITED KNOWLEDGE ON THE
SUBJECT. I TRIED THIS AND GOT AN ERROR, PROBABLY BECAUSE I DON'T KNOW WHAT
TO ELIMINATE.

I AM LOOKING FOR A DEVELOPER WHO CAN DO CONTRACT WORK (WORD VBA) FROM TIME
TO TIME. WOULD YOU OR ANYONE YOU KNOW BE INTERESTED?

THANK FOR YOUR HELP ON THIS.

 

[Helmut Weber]

Hi Cissy,

I AM LOOKING FOR A DEVELOPER WHO CAN DO
CONTRACT WORK (WORD VBA) FROM TIME
TO TIME. WOULD YOU OR ANYONE YOU KNOW
BE INTERESTED?

Yes.

Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word 2002, Windows 2000

 

[Chuck]

Hi Cissy

I'd be happy to help as a VBA contractor. Please feel free to contact me at
(e-mail address removed) and we can take it from there.

Responding to your questions:

HOW DO I LINK THE LISTINDEX OF
THE NAME FROM THE LISTBOX WITH THE DATA RECORD?

You'd use " n = frmLetterhead.lstNames.ListIndex" to get the listindex
number, and then locate the corresponding table or spreadsheet row number
(see suggested code below). The reason you're getting the last record
inserted all the time is that it's the last record read from the merge data
while populating the list box. It's simpler to populate the list box with
all the data if you can fit it into 10 columns or less, but there would be
times when that's not possible and you'd have to use the list index number to
go back to the merge data and pull it from there.

NO REASON, JUST TRYING TO PUT THIS TOGETHER WITH MY LIMITED KNOWLEDGE ON THE
SUBJECT. I TRIED THIS AND GOT AN ERROR, PROBABLY BECAUSE I DON'T KNOW WHAT
TO ELIMINATE.

Not knowing what the error you got was, here's some modified code that
should work. I can't test it properly because the document management system
where I'm at intercepts Word's mail merge and the mailmerge object doesn't
work as documented. There are other (more efficient) ways to do the things
your code does but I've kept to your methodology so as not to confuse the
issue - this simply shows how you can pull information from a merge data file
into a list box and then get the record from the list box to fill bookmarks.

Dim oDoc As Document
Dim ofrmLetterhead As frmLetterhead
Dim n As Long
Dim nCurrRecord As Long

Set ofrmLetterhead = New frmLetterhead

Set oDoc = ActiveDocument

Load frmLetterhead

'following 2 lines not necessary
'frmLetterhead.lstNames.Clear
'frmLetterhead.lstNames.SetFocus

DoEvents

oDoc.MailMerge.DataSource.ActiveRecord = wdFirstRecord
nCurrRecord = oDoc.MailMerge.DataSource.ActiveRecord
n = 0 'first row number in list box
'counting starts at 0

Do
With frmLetterhead
.lstNames.AddItem oDoc.MailMerge.DataSource.DataFields("Name")
.lstNames.List(n, 1) =
oDoc.MailMerge.DataSource.DataFields("bkDD")
.lstNames.List(n, 2) =
oDoc.MailMerge.DataSource.DataFields("bkEmail")
.lstNames.List(n, 3) =
oDoc.MailMerge.DataSource.DataFields("bkFax")
.lstNames.List(n, 4) =
oDoc.MailMerge.DataSource.DataFields("bkSig")
'etc to max 10 columns
End With
n = n + 1 'inrement row number for next record
oDoc.MailMerge.DataSource.ActiveRecord = wdNextRecord
Loop Until nCurrRecord = oDoc.MailMerge.DataSource.ActiveRecord

frmLetterhead.Show

'assuming btnOKClicked is boolean variable
'set in OK button on form
If btnOKClicked = True Then

n = frmLetterhead.lstNames.ListIndex

With ActiveDocument
.Bookmarks("bkName").Range.Text = frmLetterhead.lstNames.Column(0,
n)
.Bookmarks("bkDD").Range.Text = frmLetterhead.lstNames.Column(1, n)
.Bookmarks("bkEmail").Range.Text =
frmLetterhead.lstNames.Column(2, n)
.Bookmarks("bkFax").Range.Text = frmLetterhead.lstNames.Column(3, n)
.Bookmarks("bkSig").Range.Text = frmLetterhead.lstNames.Column(4, n)
End With

Else
ActiveDocument.Close wdDoNotSaveChanges
End If

' Destroy objects
Unload ofrmLetterhead
Set ofrmLetterhead = Nothing
Set oDoc = Nothing

Selection.GoTo What:=wdGoToBookmark, Name:="bkStop"

Best regards
Chuck Henrich

 

[Chuck]

Whoops, sorry, code I posted in immediately preceding message was missing a
line. Corrected code below - HTH:

Dim oDoc As Document
Dim ofrmLetterhead As frmLetterhead
Dim n As Long
Dim nCurrRecord As Long

Set ofrmLetterhead = New frmLetterhead

Set oDoc = ActiveDocument

Load frmLetterhead

'following 2 lines not necessary
'frmLetterhead.lstNames.Clear
'frmLetterhead.lstNames.SetFocus

DoEvents

oDoc.MailMerge.DataSource.ActiveRecord = wdFirstRecord
nCurrRecord = oDoc.MailMerge.DataSource.ActiveRecord
n = 0 'first row number in list box
'counting starts at 0

Do
With frmLetterhead
.lstNames.AddItem oDoc.MailMerge.DataSource.DataFields("Name")
.lstNames.List(n, 1) =
oDoc.MailMerge.DataSource.DataFields("bkDD")
.lstNames.List(n, 2) =
oDoc.MailMerge.DataSource.DataFields("bkEmail")
.lstNames.List(n, 3) =
oDoc.MailMerge.DataSource.DataFields("bkFax")
.lstNames.List(n, 4) =
oDoc.MailMerge.DataSource.DataFields("bkSig")
'etc to max 10 columns
End With
n = n + 1 'increment row number for next record
nCurrRecord = oDoc.MailMerge.DataSource.ActiveRecord
oDoc.MailMerge.DataSource.ActiveRecord = wdNextRecord
Loop Until nCurrRecord = oDoc.MailMerge.DataSource.ActiveRecord

frmLetterhead.Show

'assuming btnOKClicked is boolean variable
'set in OK button on form
If btnOKClicked = True Then

n = frmLetterhead.lstNames.ListIndex

With ActiveDocument
.Bookmarks("bkName").Range.Text = frmLetterhead.lstNames.Column(0,
n)
.Bookmarks("bkDD").Range.Text = frmLetterhead.lstNames.Column(1, n)
.Bookmarks("bkEmail").Range.Text =
frmLetterhead.lstNames.Column(2, n)
.Bookmarks("bkFax").Range.Text = frmLetterhead.lstNames.Column(3, n)
.Bookmarks("bkSig").Range.Text = frmLetterhead.lstNames.Column(4, n)
End With

Else
ActiveDocument.Close wdDoNotSaveChanges
End If

' Destroy objects
Unload ofrmLetterhead
Set ofrmLetterhead = Nothing
Set oDoc = Nothing
Set oDocDataSource = Nothing

Selection.GoTo What:=wdGoToBookmark, Name:="bkStop"