Microsoft Office Forums Userform: Select Item, Multiple MailMerge Fields Populate

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-11-2019, 09:54 AM
ericav68 ericav68 is offline Userform: Select Item, Multiple MailMerge Fields Populate Windows 10 Userform: Select Item, Multiple MailMerge Fields Populate Office 2016
Novice
Userform: Select Item, Multiple MailMerge Fields Populate
 
Join Date: May 2019
Posts: 3
ericav68 is on a distinguished road
Default Userform: Select Item, Multiple MailMerge Fields Populate

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

Last edited by ericav68; 05-12-2019 at 06:23 AM.
Reply With Quote
  #2  
Old 05-11-2019, 04:09 PM
macropod's Avatar
macropod macropod is offline Userform: Select Item, Multiple MailMerge Fields Populate Windows 7 64bit Userform: Select Item, Multiple MailMerge Fields Populate Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,533
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by ericav68 View Post
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...
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 05-11-2019, 04:43 PM
ericav68 ericav68 is offline Userform: Select Item, Multiple MailMerge Fields Populate Windows 10 Userform: Select Item, Multiple MailMerge Fields Populate Office 2016
Novice
Userform: Select Item, Multiple MailMerge Fields Populate
 
Join Date: May 2019
Posts: 3
ericav68 is on a distinguished road
Default

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.

Reply With Quote
  #4  
Old 05-11-2019, 06:01 PM
eduzs eduzs is offline Userform: Select Item, Multiple MailMerge Fields Populate Windows 10 Userform: Select Item, Multiple MailMerge Fields Populate Office 2010 32bit
Competent Performer
 
Join Date: May 2017
Posts: 157
eduzs is on a distinguished road
Default

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.
__________________
Backup your original file before doing any modification, test in a throwaway copy of your file.
Reply With Quote
  #5  
Old 05-11-2019, 06:29 PM
ericav68 ericav68 is offline Userform: Select Item, Multiple MailMerge Fields Populate Windows 10 Userform: Select Item, Multiple MailMerge Fields Populate Office 2016
Novice
Userform: Select Item, Multiple MailMerge Fields Populate
 
Join Date: May 2019
Posts: 3
ericav68 is on a distinguished road
Default

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

Last edited by ericav68; 05-11-2019 at 06:49 PM. Reason: i fixed it
Reply With Quote
Reply

Tags
docvariable, mailmerge, userform

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to populate a userform combobox from an excel worksheet jrooney7 Word VBA 14 09-16-2018 08:52 PM
Userform calls other userform, then populate worksheet Lehoi Excel Programming 0 02-03-2016 02:58 PM
Trying to populate Word bookmarks with excel UserForm smd1112 Excel Programming 7 09-03-2014 09:42 PM
auto populate fields for multiple files w/in folder jbyrd Word 0 07-21-2014 07:35 AM
Userform: Select Item, Multiple MailMerge Fields Populate Automatically select first item in drop-down? flackend Excel 4 08-29-2011 02:07 PM


All times are GMT -7. The time now is 05:14 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft