Creating a function

[red6000]

Hi,

I have a load of forms which are essentially Identical and contain 5
textboxes.

In my code I then make use of the values held in the textboxes along with
some other standard code:

What I would like to do is create a function to make my code more efficient.
where the argument of the function is the formname.

Ie, for example I have 5 forms named (in reality I have numerous forms)

formA
formB
formc
formD
formE

each form holds 6 textboxes (textbox6 thru to 11).

Now my function I have created is:

Function addAddress(frmName As String)

Dim TB6 As String
Dim TB7 As String
Dim TB8 As String
Dim TB9 As String
Dim TB10 As String
Dim TB11 As String

TB6 = frmName & ".TextBox6.Value"
TB7 = frmName & ".TextBox7.Value"
TB8 = frmName & ".TextBox8.Value"
TB9 = frmName & ".TextBox9.Value"
TB10 = frmName & ".TextBox10.Value"
TB11 = frmName & ".TextBox11.Value"

Documents("BaseLetter.doc").Activate

Selection.Find.ClearFormatting
Selection.Find.Text = "aaa"
Selection.Find.Execute
Selection.Delete Unit:=wdCharacter, count:=1

Selection.TypeText Text:=TB6
Selection.TypeParagraph
Selection.TypeText Text:=TB7
Selection.TypeParagraph
Selection.TypeText Text:=TB8
Selection.TypeParagraph
Selection.TypeText Text:=TB9
Selection.TypeParagraph
Selection.TypeText Text:=TB10
Selection.MoveRight Unit:=wdCell
Selection.ParagraphFormat.Alignment = wdAlignParagraphRight
Selection.TypeText Text:="rgt"

Selection.Find.Text = "ddd"
Selection.Find.Execute
Selection.Delete Unit:=wdCharacter, count:=1
Selection.TypeText Text:=TB11

Selection.Find.Text = "eee"
Selection.Find.Execute
Selection.Delete Unit:=wdCharacter, count:=1
Selection.TypeText Text:="insert"

Selection.Find.Text = "bbb"
Selection.Find.Execute
Selection.Delete Unit:=wdCharacter, count:=1

End Function

but when I run it by calling the function with:

addAddress ("formA")

it populates with the text of 'formA.Textbox6.Value' instead of the value
held in textbox 6 etc.

How can I build the correct name (I know it is to do with using the argument
of String, but what should I use)?

Thanks.

 

[Doug Robbins - Word MVP]

In that you have ".TextBox6.Value" enclosed in quote marks, it will be
treated as a string.

Apart from not being sure that is your only problem, instead of doing the
Selection.Find business, you should either insert Bookmarks into the
template and then insert the text into the .Range of the bookmarks, or
insert {DOCVARIABLE} fields into the document and use code to set the value
of the variables and update the fields in the document.


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[steve.allen75]

In that you have ".TextBox6.Value" enclosed in quote marks, it will be
treated as a string.

Apart from not being sure that is your only problem, instead of doing the
Selection.Find business, you should either insert Bookmarks into the
template and then insert the text into the .Range of the bookmarks, or
insert {DOCVARIABLE} fields into the document and use code to set the value
of the variables and update the fields in the document.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
























- Show quoted text -

Hi thanks for the reply. I appreciate that the rest of my code is not
the best, but I am trying to just get it to work generally first and
then improve later.

I've changed the code to remove the "" around .TextBox6.Value, but
that errors with Invalid or unqualified reference. I assume because I
still have the arguments set as type String.

Ignoring the rest of the code I am effectively am looking for the code
to do the following:

Dim xForm As something (where x is the name of a userform)
Dim zValue As something (where z is the value of a textbox)

zValue = xForm.TextBox6.Value

MsgBox("The value held in TextBox 6 in " & xForm & " is " zValue)

 

[red6000]

Hi thanks for the reply. I appreciate that the rest of my code is not
the best, but I am trying to just get it to work generally first and
then improve later.

I've changed the code to remove the "" around .TextBox6.Value, but
that errors with Invalid or unqualified reference. I assume because I
still have the arguments set as type String.


Ignoring the rest of the code I am effectively am looking for the code
to do the following:


Dim xForm As something (where x is the name of a userform)
Dim zValue As something (where z is the value of a textbox)


zValue = xForm.TextBox6.Value


MsgBox("The value held in TextBox 6 in " & xForm & " is " zValue)