|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Creating a letter in Word, with text pulled from an Excel document based on selections in a userform
Hi everyone;
I apologize in advance if this question has already been answered somewhere in the forums - I did a few searches but had no luck finding what I was looking for. I'm new to coding in VBA, and am looking to find a way to create a letter in Microsoft Word with a standard paragraph at the beginning and another at the end, but with the paragraphs in the middle pulled from an excel spreadsheet based on selections made in a userform within word. ---------------------- The letter would look something like this: Name Address Dear Sir/Madam; Beginning Paragraph: This paragraph will appear at the beginning of every letter. Middle paragraphs will be generated here based on answers to questions on the userform: RE101 (if checked on userform) RE102 (if checked on userform) RE103 (if checked on userform) Ending Paragraph: This ending paragraph will appear at the end of every letter. Sincerely, TMAL ---------------------- The userform will ideally be just a simple series of checkboxes: RE101: □ Add to letter RE102: □ Add to letter RE103: □ Add to letter ---------------------- The excel spreadsheet will be set up something like this: In Column A: Labels for the associated paragraph (RE101, RE102, RE103, etc) In Column B: Paragraphs of text to be inserted into the document (Text for RE101, Text for RE102, Text for RE103, etc) ---------------------- Any help you can provide will be much appreciated - I'm quite new to VBA and don't really know where to start. Thanks!! TMAL |
#2
|
||||
|
||||
From what you've described, I can't see any reason for Excel to be involved - the variable content could be stored in the template containing the userform.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Hey! Thanks for the reply.
The reason I would like to keep the variable content in excel is that there is already an existing excel spreadsheet that I want to pull the paragraphs of text from. The text in these paragraphs may be periodically modified on the spreadsheet by other users. To be honest though, I'm not sure how I would proceed even if I did store the variable content in the template containing the userform. I'm still quite new to VBA and have so far been able to get away with sticking together different pieces of code I've found online. Any further direction you can provide is greatly appreciated. Thanks again! |
#4
|
||||
|
||||
See the attached Word template. It contains a simple userform with 3 checkboxes for conditionally inserting some content. The userform checkboxes update custom document properties and the conditional content is managed by fields in the document that test the state of those properties. Once the 'commit' button is clicked, the fields are converted to text and userform closes. If users need to customise the text for a particular letter, that can be done after the userform closes. If you need to change the default boilerplate text itself, that can be done by editing the fields in the template; they can be accessed by opening the template for editing and pressing Alt-F9.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
This is absolutely perfect. Thanks so much for your help!
I actually came across a reply of yours in another thread from 2013 last night when I was trying to solve a problem I was having with saving the custom document properties to the template. Very helpful responses there as well! Thanks again! |
#6
|
|||
|
|||
Follow-up question; this may not be related.
After working on the above template I went back to working on another project that I had completed about a month ago to put in some minor updates. It's a word document with about 100 questions, with radio buttons and checkboxes used for the various responses. I have VB code so that when a button is selected, the associated text changes to green (for positive responses) or red (for negative responses); however, when I opened the document today, none of those colour changes appear to be working. There don't appear to be any issues with the code itself, but I can't figure out for the life of me why it's not working. Most of the code would look something like this: Code:
Private Sub CheckBox223_Click() If CheckBox223.Value = vbChecked Then CheckBox223.ForeColor = vbBlack Else: CheckBox223.ForeColor = RGB(0, 128, 0) End If End Sub Code:
Private Sub OptionButton61_Change() With OptionButton61 If .Value Then .ForeColor = RGB(0, 128, 0) Else .ForeColor = vbWindowText End If End With End Sub |
#7
|
||||
|
||||
Have you tried repairing the Office installation (via Windows Control Panel > Programs > Programs & Features > Microsoft Office (version) > Change > Repair)?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Change colours in different tables based on dropdown selections | kevinbradley57 | Word VBA | 10 | 08-09-2017 03:54 PM |
Excel to Word data import based on UserForm entry | jhancock1994 | Word VBA | 8 | 05-16-2017 04:41 PM |
Help with Hide/Display of sections of document based on Drop Down selections | turnquistcm | Word | 2 | 05-03-2017 07:05 PM |
Need help creating a Word document that is populated based on certain selected values | alidaanish | Word | 1 | 01-10-2014 10:44 PM |
Creating a shared template with selections | gmsp26 | Word | 1 | 04-14-2011 05:18 PM |