PDA

View Full Version : [SOLVED] userform text box formatting and exporting to word template



Chunk
10-06-2015, 09:47 AM
Hi everyone,

I have a userform with an "address" text box. Don't know if it makes a difference but the userform was created through excel.
I want the user to be able to use the return key in the text box to have the address appear as follows (on the userform):

John Wayne
123 Easy St
Pocohontas, NE
87598

I would like to then copy the address to the document in the same format.
I can get the text to copy from the userform to the document, just not with the desired formatting. Any ideas? Here is the code I am currently working with:

Private Sub btn_Continue_Click()

Dim wrdApp As Object
Dim wrdNNDF As Object

Set wrdApp = CreateObject("Word.Application")
Set wrdNNDF = wrdApp.Documents.Open("C:\template.doc")
wrdNNDF.Activate

wrdApp.Selection.Find.ClearFormatting
wrdApp.Selection.Find.Replacement.ClearFormatting

wrdNNDF.doc_RR_tbox_address.Text = TESTMASTER_optbut.tbox_address.Text


wrdNNDF.PrintOut
wrdNNDF.Close SaveChanges:=wdDoNotSaveChanges
wrdApp.Quit
Unload Me
End Sub

SamT
10-06-2015, 04:26 PM
It's easier to use
tbxName
tbxAdd1
tbxAdd2
tbxCity
tbxState
tbxZip
In both Excel and Word UserForms and documents


With wrdNNDF
.doc_RR_tbox_Name = Me.tbxName
.doc_RR_tbox_Add1 = Me.tbxAdd1
'Etc
End With


strAddress = tbxName & vbCrLf & tbxAdd1 & vbCrLf
If Not tbxAdd2 = "" Then strAddress = strAddress & tbxAdd2 & vbCrLf
strAddress = strAddress & tbxCity & ", " & tbxState & vbCrLf & tbxZip

'Something = strAddress

That may not be feasible, I don't know how you are setting the value of tbox_adddress. I suspect that the linefeed character(s) used in tbox_address are not being recognized by Word's address box.Somehow, you will have to discover which linefeed chars are used in tbox_address and which one Word's address box uses and match them up.

try this in

Sub tbox_address_AfterUpdate()
MsgBox "vbCr " & Instr(tbox_address, vbCr) > 0
MsgBox "vbLf " & Instr(tbox_address, vbLF) > 0
MsgBox "vbCrLf " & Instr(tbox_address, vbCrLF) > 0
End Sub



Now you understand why it is not good to use a single control for multiple Data Fields

snb
10-07-2015, 01:36 AM
Use the Builtin tools in Word.

Put a docvariable field with the name 'address' in the word document where you want the address to appear

In Excel


Sub M_snb()
with getobject("C:\template.doc")
.variables("address")=join(array("Name","street","State","zip"), vbcr)
.fields.update
.printout false
.close 0
end with
End Sub