Conditional Entry

[newk]

I am struggling with an address, in that I have an AddressLine1...AddressLine5 dialog box and a bunch of bookmarks one under the other in an address formation.

Due to the variation of UK addresses maybe only 3 of the Lines will be used. If this happens I have a 2 line gap between the address and the Postcode [ZIP code], which frankly looks unprofessional.

Please could someone suggest how to make the dialog box delete the bookmark and the line if that box has been left blank making the address look smart.

[Bilby]

Newk, How about using only one bookmark and writing a string to it?

The following assumes that you have 5 default textboxes on a userform
and one bookmark on your document. If a textbox has anything in it then its added to a string. When all controls have been checked the string is written to the bookmark.

[VBA]Dim oText As Control
Dim sText As String
sText = ""
For Each oText In Me.Controls ' me is the active form
' test each control to see if its name is TextBoxxx
If Left(oText.Name, 7) = "TextBox" And oText.Value <> "" Then
sText = sText & oText.Value & Chr(13)
End If
Next oText
sText = Left(sText, Len(sText) - 1) ' remove last return
Selection.GoTo What:=wdGoToBookmark, Name:="BookAlt1"
Selection.TypeText Text:=sText
[/VBA]

[Bilby]

rethinking the actual question you asked,
Goto the bookmark, If there's no valid address then use Selection.Delete instead of a string. This should delete the bookmark and the empty line.

[newk]

Thanks Bilby, I'll give them both ago as I am really wanting to learn VBA and 'doing' is the best way of 'learning'.

The second option sounds the easiest and probably the tidiest.
Thanks ever so much for your ideas and all your time. I'll let you know how I get on.

[mdmackillop]

Hi Newk,
Personally I would go with Bilby's first answer as it simplifies the creation of future documents, and also allows for 6 line addresses by the simple addition of another textbox on the form. If you have a lot of documents set up with the multiple bookmarks, these can be "cleaned up" by some simple code if necessary.
MD

[newk]

Thanks MD, I will work with option 1.

9 days on and despite everyones huge help I can't get the code to work. I have tried to patch Bilby's code to my doc but I'm doing at least one thing wrong. If someone could have a look at it and let me know I'd be most grateful.

Am I supposed to repeat part of the code for each textbox?

[mdmackillop]

Hi Newk,

Have you had a look at this KB Entry, which may give you some further ideas.
http://www.vbaexpress.com/kb/getarticle.php?kb_id=184
MD

[mdmackillop]

Try the following revision to Bilby's code (as you have more textboxes than used for address purposes).
BTW, You should remove genuine email addresses and phone numbers from your zip file. A few made up samples will suffice for test purposes.

[VBA] Dim sText As String
sText = ""
For i = 1 To 7
If frmDisclaimerForm("Textbox" & i).Text <> "" Then
sText = sText & frmDisclaimerForm("Textbox" & i).Text & Chr(13)
End If
Next
'MsgBox sText
sText = Left(sText, Len(sText) - 1) ' remove last return
Selection.GoTo What:=wdGoToBookmark, Name:="Address"
Selection.TypeText Text:=sText
[/VBA]

[newk]

Thanks MD, Thats really cool. It's so much easier to understand the code when your variables are in the right place. I'm going to write it up for another couple of documents and try and get to grips with it.

I would like to say, (and this probably isn't the right place),but it's impossible to thank you guys enough for all the help I've received from everyone at this site for all my questions. You guys are 24 karat.