Evolving form name for use in Address Block

[Jackpotgee]

Hi i am experimenting with the following code and have come unstuck.


Dim AddVar As String
Dim CLAddress As String
Dim AddCount As Long
Dim FrmStr As String
Dim ClCount As Long

CLAddress = ""
ClCount = 1
AddCount = 0
AddVar = ""
FrmStr = ""


Do While AddCount < 8
AddCount = AddCount + 1
AddVar = "Addr" & AddCount
CLAddress = "Address" & ClCount
FrmStr = "FrmNewClient." & AddVar & ".Value"
If FrmStr = "a" Then
ActiveDocument.FormFields(CLAddress).Result = FrmStr
ClCount = ClCount + 1
Else
End If
Loop


The idea is - I have a user form with 8 possible Address lines - FrmNewClient.
The address lines on the user form are named Addr1, Addr2.....Addr8.

I want the form to function a little like a Mail Merge and ignore any lines of address that are left blank. - i.e only update the active document where there has been data entered on the form. - but i dont want loads of empty gaps everywhere.

The problem seems to be that my str that cycles through the Addr lines is not being recongised as a form by the If statement. -

Any ideas how i can fix this? -

[fumei]

Huh?

FrmStr = "FrmNewClient." & AddVar & ".Value"
If FrmStr = "a" Then



If you explicitly set FrmStr as "FrmNewClient." & AddVar & ".Value" , how can it ever be "a"????

"the Addr lines is not being recongised as a form by the If statement"

What do you mean by recognized as a form?

[Jackpotgee]

Hi, I know i am going about it the wrong way

The idea is that FrmStr = FrmNewClient.Addr1.Value

So....

If FrmNewClient.Addr1.Value = "a" then.....




Everytime the loop repeats i want the If statement to look at the next Addr line on the form. I.e. Addr2, Addr3 etc...

Hence i was trying to set up a variable that increments as it loops, which FrmStr does perfectly.

However, FrmStr is only a string and so when i try and use it to mean a form field it isnt working.

[fumei]

"However, FrmStr is only a string and so when i try and use it to mean a form field it isnt working."

Of course it won't, because it IS a string.

I do not know what you mean by Addr line. I think you mean some control on the userform, which has names like Addr1, Addr2...etc.

Use a control object.

Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Name = "Addr1" Then



However, I suspect that you are not, in fact, talking about a user form - even though you state "user form"

I suspect you are talking about formfields. But it is hard to say, as you are using .Value, which would be a control on a userform.

You will get more help if you clearly state your situation.

[Jackpotgee]

Sorry,

Yes i am using a User Form. The User form has 8 text boxes named Addr1, Addr2, Addr3...... Addr8.

The user can fill in any of the 8 lines of address, but does not have to fill in all of them. When the code is executed the address lines will be appended to the document in the formfields Address1,Address2,Address3.....Address8

If they only fill in Addr1, Addr4 and Addr8 on the userform - I do not want the document to read:


101 abc way


X Town



UK




I want it to read:


101 abc way
X Town
UK




That is why i went for a looping increment. So the each of the fields on the userform will be looked at in turn to establish if they have any data in them before adding them to the document. If they are empty, the code will skip to the next field on the userform.


If FrmStr = "a" Then
ActiveDocument.FormFields(CLAddress).Result = FrmStr
ClCount = ClCount + 1
Else



CLAddress seen above is another incrementing control That only increments if the code has run (i.e. if there was data in the field just examined)

Hope this clarifies.

I will try your control suggestion anyway and post back if i can get it to work.

Many thanks

[Jackpotgee]

Ok i have it nearly working thanks to your suggestion of the control above.

What i have now is:




Dim Ctr As Control

For Each Ctr In FrmNewClient.Controls

If Ctr = "a" Then
ActiveDocument.FormFields("ClientAddress").Result = ActiveDocument.FormFields("ClientAddress").Result & Ctr & Chr(13)
Exit For
MsgBox "no"
GoTo ExitProc
End If
Next


This like i say is very nearly working. The code is cycling through the fields on the user form perfectly. The only problem now is that instead of producing a carriage return between entries i am instead receving the symbol of a square. - produced by the code "Chr(13)".

My resulting formfield in my document reads as:

a[]a[]a[]

rather than:

a
a
a

- Do you know how i can fix this?

Please bear in mind that the character "a" is only a test piece of data.

[fumei]

1. Please use the underscore character to break up long lines of code. Thanks.

2. use vbCrLf rather than Chr(13)

3. I would be careful with:
For Each Ctr In FrmNewClient.Controls
If Ctr = "a" Then



Why? Because you are using the default property of Ctr, which is .Value. In this case, it is OK to a point, as you want the text (I think).

But....

1. For Each Ctr means exactly that, for EACH. So all controls will be tested in the loop. Commandbuttons, or whatever.

Say it was a commandbutton, then Ctr can NEVER = "a", because the default property of a commandbutton is .Value as well, but .Value (for a commandbutton) is NOT text, but Boolean.

Ctr for commanbutton will return True or False.

Also:
ActiveDocument.FormFields("ClientAddress").Result = _
ActiveDocument.FormFields("ClientAddress").Result & _
Ctr & Chr(13)
while not an error (because it will not be executed unless Ctr = "a"), it is a little sloppy, and I would recommend being more explicit.

Use the .Text.property of the control, and further, you may want to test to see if the control is indeed a textbox. nd not some other control.

Using vbCrLf should help, but I am a little confused. Your code seems to put all the text into ONE formfield. Your post seems to imply you are trying to put text into multiple formfields.

Note the use of the underscore character to break uo the line.

[Jackpotgee]

Thanks for your continuing help on this matter.

1. - Noted. Thank you.
2. - Unfortunately vbCrLF produces the same result as Chr(13) and vbnewline

3. - The control variable is new to me so this is all quite interesting. You are correct in thinking i am trying to test the text value of the control. However i cannot find the .Text property that you mentioned in the properties list for the control?

Also:

Eventually the code will just test whether there has been any text entered in the userform field at all:

I.e. - If ctr = "*" then.... - or the equivalant.

NOT - If ctr = "a" then....


Regarding the code putting all of the text in one formfield. This was a change i made to my original idea whilst playing about - I thought that if i could get all of the text to be entered into one formfield with a newline/carrgiage return following each entry, i would get a more efficient result.

I dont understand why i cant get VBA to produce an actual carriage return in the formfield instead of its character representation [].

Thanks Again

[Jackpotgee]

Ok i have sorted the problem i was having with the carriage return being represented as a box

Simply use this code after each update


selection.fields.update


However i am now stuck on the problem of testing to see if the control is a text box as you mentioned in your previous reply. How would i go about this?

I Currently have


If Ctr Like "*" Then
ActiveDocument.FormFields("ClientAddress").Result = _
ActiveDocument.FormFields("ClientAddress").Result & Ctr & Chr(13)
Selection.Fields.Update


This is obviously looking at all the controls on the userform as you stated it would.

I have tried various versions of

If Ctr.name like "Addr*" and ctr like "*" then....

If (ctr.name = "Addr1" or ctr.name = "Addr2" or.....) and ctr like "*" then....


You obviously have a trick up your sleave for testing if the control is a text box?

[fumei]

1. Why are you using Like?

2. Why are you even using a formfield in the document? Formfields are for user input. Is the user going to change (input) possible alternative text? If they are NOT, then...why use a formfield at all?

3. "However i cannot find the .Text property that you mentioned in the properties list for the control?"

Huh? Are you saying you can not see the .Text property of....a TextBox? Not possible. You are not looking properly. As you did not state how you looked for it i can not really tell how you missed it. (Although I have a guess.) However, a TextBox most certainly does have a .Text property. All of them.

4. controls have a Type, and can be tested using (there are other ways...):

Dim ctl As Control
For Each ctl In Me.Controls
If TypeOf ctl Is MSForms.TextBox Then



5. I would warn you again that using

If Ctr Like "*" Then

is not a good idea for a beginning user of VBA. You do not - and I do not mean this in any demeaning way - really understand what you are doing. It is better to be clear and explicit.
Dim strA As String
Dim strB As String
Dim result

strA = "* "
strB = " *"

result = strA Like strB
MsgBox result

What do you think will be displayed by the messagebox? It will display False, as "* " is NOT Like " *".

6. To avoid the [], use vbCr, not vbCrLf or Chr(13). The square box is actually ASCII 10, or Lf.


ActiveDocument.FormFields("Text1").Result = _
"blah blah yadda" & _
vbCr & vbCr & "third line"

produces:

blah blah yadda

third line



No square boxes.

[fumei]

Just want to add that
If Ctr Like "*" Then
ActiveDocument.FormFields("ClientAddress").Result = _
ActiveDocument.FormFields("ClientAddress").Result &_
Ctr & Chr(13)
Selection.Fields.Update

is not good either. There is nothing to indicate what IS the Selection. What if the Selection is not in that formfield? It is better to not even use Selection.

[Jackpotgee]

Thanks again,

1.
Here is what i have - which is currently working, finally. Although it still uses "Like" which i know you are opposed to...


For Each Ctr In FrmNewClient.Controls
If TypeOf Ctr Is MSForms.TextBox _
And Ctr.Name Like "A*" And Ctr Like "?*" Then
ActiveDocument.FormFields("ClientAddress").Result = _
ActiveDocument.FormFields("ClientAddress").Result & Ctr & vbCr
End If

Next



I am using like because i am trying to compare an indefinite name or result.
For example - in the code above

...And Ctr.Name Like "A*" - This is becuase i only want it to look at the textboxes on the userform that are named Addr1, Addr2, Addr3...... Addr8. - There are other text boxes on the userform that i do not want the code to look at.

...And Ctr like "?*" - This is so it only looks at the text boxes on the userform that have some data written in them.

I'm afraid i do not know of another way to achieve this. Why is using "Like "wildcard"" not a good way of achieving results?

2. The entire document is controlled by userforms. The user cannot edit any part without using a form to do so. How would you transfer the data entered on a userform onto a document without the use of formfields?

3. When i say i cant find the .text property - I know that textboxes all have a .text property but the problem came i presume becuase Ctr is set as a control - not specifically a text box so when I went to enter "Ctr.Text =..." in my code it didnt work becuase .Text is not a property of a control.

4. The whole issue with the [] has now been resolved but for some reason it appears to be that none of the codes i have tried - vbCr, vbCrLf, CHR(13) etc... work unless there is a 'hard enter' keyed into the document after the formfield. By this i mean the formfield being directly followed by a carriage return in the document. If you delete the carriage return after the data has been appended to the document it reverts to its old tricks -i.e. [] in between the address lines. Wierd?


-How would you alter the code above if you were writting this?

[fumei]

"How would you transfer the data entered on a userform onto a document without the use of formfields? "

You can transfer text (data) to where ever you want in a document without formfields. Bookmarks.

"the problem came i presume becuase Ctr is set as a control - not specifically a text box so when I went to enter "Ctr.Text =..." in my code it didnt work becuase .Text is not a property of a control. "

Absolutely correct. You can access the actual properties of the control by:

Dim ctl As Control
For Each ctl In Me.Controls
Me.Controls(ctl.name).actual properties

' for example if the control IS a textbox then

Me.Controls(ctl.name).Text

is valid syntax.

"...And Ctr.Name Like "A*" - This is becuase i only want it to look at the textboxes on the userform that are named Addr1, Addr2, Addr3...... Addr8. - There are other text boxes on the userform that i do not want the code to look at."

Ah, very good. Makes sense. Although there are other ways to achieve the same idea.

"...And Ctr like "?*" - This is so it only looks at the text boxes on the userform that have some data written in them. "

Ah, this I would not do. A textbox with something in it is NOT "", so....

If ctl <> "" Then

means IF ctl (the text content of the textbox) is NOT an empty string THEN...

[Jackpotgee]

If ctl <> "" Then
Excellent! - I have been wondering about the correct way to achieve the opposite of "Like"

I will have a look into Bookmarks - Can you still select the text from the bookmark and bring it back into a user form for further editing as you can with formfields?

i.e. userform.textbox1.value = activedocument.formfields("field1").result


Me.Controls(ctl.name).Text
I will have a look into this as well tomorrow as its home time now!

Thanks for all your help so far.

[fumei]

"Can you still select the text from the bookmark and bring it back into a user form for further editing as you can with formfields?"

Yes, absolutely.

userform.textbox1.value = activedocument.Bookmarks("field1").Range.Text

I bring text from Bookmarks into userforms all the time.

[Jackpotgee]

Ok, - why is it that Bookmarks are superior to Formfields?

It would be a rather time consuming change in my document but one i am happy to do if it is worth it!

[fumei]

I am not saying Bookmarks are superior to FormFields.

What I am saying is that formfields are generally used for user input. That is what they are designed for. If the user is NOT doing input, then formfields are not needed. Yes, they can be used, and in some cases, it would in fact be better to use them.

However....

It is simply a question of what you want to DO.

If what you want to do is place text at a specific location, then...you can do that by using bookmarks. That is what bookmarks are for - marking a specific location.

BTW: "I have been wondering about the correct way to achieve the opposite of "Like"

Huh? If ctl <> "" is not the opposite of Like. I am not even sure what you mean by "opposite". It is a Boolean statement (i.e. True or False) regarding ctl.

IF ctl is blank ( = "") then the IF returns False.

IF ctl is not blank (<> "") then the IF returns True.

[Jackpotgee]

BTW: "I have been wondering about the correct way to achieve the opposite of "Like"

I did not mean a literal opposite, simply that there is not a "Not Like" or "not equal to" function within vba (or doesnt appear to be) and that i had been wondering how to go about achieving it.

Thanks again for all your help.

[fumei]

"there is not a "Not Like" or "not equal to" "

<> means precisely not equal to

BTW - 1: Not Like is VERY different conceptually, logically, from Not Equal To. It would be a mistake to think of them the same way.

BTW - 2: <> applies to any expression, not just strings.

If myBoolean1 <> myBoolean2 Then

If myBoolean1 = True, and myBoolean2 = True, then the above will return False. The IF statement is testing IF myBooleans is not equal to myBoolean2.

As they ARE equal, the IF statement is False.

If myBoolean1 = False, and myBooleans2 = False, the IF statement also returns False. Because, again, IF statements do not test the values of the parameters themselves. They ONLY test the relative values.

For the above code, ALL of the conditions below will return True.

myBoolean1 = True, myBoolean2 = False
myBoolean1 = False, myBoolean2 = True

All of these will return False:

myBoolean1 = True, myBoolean2 = True
myBoolean1 = False, myBoolean2 = False