Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-30-2017, 07:29 AM
TMAL TMAL is offline Creating a letter in Word, with text pulled from an Excel document based on selections in a userform Windows 7 32bit Creating a letter in Word, with text pulled from an Excel document based on selections in a userform Office 2010 32bit
Novice
Creating a letter in Word, with text pulled from an Excel document based on selections in a userform
 
Join Date: Nov 2017
Location: Halifax, NS, Canada
Posts: 4
TMAL is on a distinguished road
Question 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
Reply With Quote
  #2  
Old 11-30-2017, 01:37 PM
macropod's Avatar
macropod macropod is offline Creating a letter in Word, with text pulled from an Excel document based on selections in a userform Windows 7 64bit Creating a letter in Word, with text pulled from an Excel document based on selections in a userform Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 12-12-2017, 09:45 AM
TMAL TMAL is offline Creating a letter in Word, with text pulled from an Excel document based on selections in a userform Windows 7 32bit Creating a letter in Word, with text pulled from an Excel document based on selections in a userform Office 2010 32bit
Novice
Creating a letter in Word, with text pulled from an Excel document based on selections in a userform
 
Join Date: Nov 2017
Location: Halifax, NS, Canada
Posts: 4
TMAL is on a distinguished road
Default

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!
Reply With Quote
  #4  
Old 12-12-2017, 04:13 PM
macropod's Avatar
macropod macropod is offline Creating a letter in Word, with text pulled from an Excel document based on selections in a userform Windows 7 64bit Creating a letter in Word, with text pulled from an Excel document based on selections in a userform Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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.
Attached Files
File Type: dotm UserformDemo.dotm (33.2 KB, 24 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 12-15-2017, 06:55 AM
TMAL TMAL is offline Creating a letter in Word, with text pulled from an Excel document based on selections in a userform Windows 7 32bit Creating a letter in Word, with text pulled from an Excel document based on selections in a userform Office 2010 32bit
Novice
Creating a letter in Word, with text pulled from an Excel document based on selections in a userform
 
Join Date: Nov 2017
Location: Halifax, NS, Canada
Posts: 4
TMAL is on a distinguished road
Default

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!
Reply With Quote
  #6  
Old 12-15-2017, 09:26 AM
TMAL TMAL is offline Creating a letter in Word, with text pulled from an Excel document based on selections in a userform Windows 7 32bit Creating a letter in Word, with text pulled from an Excel document based on selections in a userform Office 2010 32bit
Novice
Creating a letter in Word, with text pulled from an Excel document based on selections in a userform
 
Join Date: Nov 2017
Location: Halifax, NS, Canada
Posts: 4
TMAL is on a distinguished road
Default

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
or

Code:
 
Private Sub OptionButton61_Change()
 With OptionButton61
 If .Value Then
 .ForeColor = RGB(0, 128, 0)
 Else
 .ForeColor = vbWindowText 
 End If
 End With
 End Sub
I'm stumped. I spoke with a few of my colleagues that I had designed the document for, and it appears that the colour changes are still working on their end. Hope I'm not missing something super obvious here.
Reply With Quote
  #7  
Old 12-15-2017, 02:16 PM
macropod's Avatar
macropod macropod is offline Creating a letter in Word, with text pulled from an Excel document based on selections in a userform Windows 7 64bit Creating a letter in Word, with text pulled from an Excel document based on selections in a userform Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by TMAL View Post
I'm stumped. I spoke with a few of my colleagues that I had designed the document for, and it appears that the colour changes are still working on their end. Hope I'm not missing something super obvious here.
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]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating a letter in Word, with text pulled from an Excel document based on selections in a userform 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
Creating a letter in Word, with text pulled from an Excel document based on selections in a userform Help with Hide/Display of sections of document based on Drop Down selections turnquistcm Word 2 05-03-2017 07:05 PM
Creating a letter in Word, with text pulled from an Excel document based on selections in a userform 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 letter in Word, with text pulled from an Excel document based on selections in a userform Creating a shared template with selections gmsp26 Word 1 04-14-2011 05:18 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:43 PM.


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