Userform: Select Item, Multiple MailMerge Fields Populate

[ericav68]

EDIT: This must have been my lucky charm. I figured the whole thing out, and I will post the entire code for it in case anyone else has this issue. I'm taking a break from it today because I worked all night, but will post up the code during the week.




I've been wracking my brain and searching the internet to try to do this with no luck.
I have a bunch of templates I need to design, I'll start with the most complicated one because it has everything. The idea is that the selections from the user form will populate both from an excel document and user defined criteria. So essentially mail merge stuff with user criteria.

I would like a user form to pop up as soon as the document is open. The user form will have the following items:

What is the Property Code? (Drop Down List)
What is the F Score? (User typed Text)
What is the P Score? (User typed Text)
What is the M Score? (User typed Text)
What is the C Score? (User typed Text)

Then a checkbox series, [] indicate a check box
Does the property need Counseling for []F []P []M []C

In the letter/template this is the gist of it:

Property Manager
Property Address
Property Name

Dear Property Manager:

On (FYE) your property scored 1 out of 25 for F, 1 out of 25 for P, 1 out of 25 for M, 1 out of 5 for C. Therefore your property needs counseling.

[F]
[P]
[M]
[C]


--------------------------

What I would like is the drop down list to show the property code from the excel source titled "Source" When the user selects the property code, then the Property Manager, Address, Name, Repeat of Property Manager and their FYE all autopopulate as if it were a regular mailmerge.

The scores would populate from the userform text box, these are not stored in the excel file.

Then if it's checked that they need counseling, an autotext paragraph will populate for the reason they need counseling F,P,M,C.

I have a userform that employs all but the autotext with bookmarks, but I want to make this more automated, so that the only thing the user has to do is type the property code and then all corresponding information like name, address, FYE. Right now I'm having them type in all the information in the userform text field which populates my bookmarks.

Other templates will pull other information like units, etc. I think mail merge fields is the only way to have dependent variables like this. I'd rather them not have to select the excel sheet like mail merge. I used GetObject to pull the information from a closed excel book, but I couldn't coordinate my bookmarks.

Thank you!!!

[macropod]

Originally Posted by ericav68

The scores would populate from the userform text box, these are not stored in the excel file.

So why not store the scores in Excel? The data have to come from somewhere...

[ericav68]

It's not that I can't put the scores in, it's just that the scores are always changing, we're talking about a few thousand scores and my main excel file feeds to a lot of other files, so I just had it set up with the static information.

The few things I've been playing around with are if statements, bookmarks, cross references and mail merge fields, but for some reason the mail merge stuff just never populates properly. I'm at the point now where I can't even think clearly anymore.

[eduzs]

Hi there.
If the main data is in Excel, I suppose that it is better to start (from the main code) with Excel and just call Word within Excel to create the document, using DOC VARIABLES that will be set based on the excel cells values.
If you want to create a single personalized document, I think that is not necessary to use mailmerge, but only fields / variables in a word document.

[ericav68]

I'll consider that, I'm giving myself a little longer to work through this.


EDIT: SOLVED THE BELOW PROBLEM I had the spreadsheet open, I'm an idiot.
Right now I'm trying to populate the list box from excel but I keep getting error 3450. I f8'd through it and apparently my problem is right here
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With