Microsoft Word VBA multiple addresses - text box

Question

Apologies in advance if this is already a question.

I've developed a user form to auto populate some of the letters we send to stakeholders. I currently have an address section in the userform - textboxstreet textboxsuburb etc. User form

in certain circumstances I need to have:

Address A - the address we are sending the letter to 
and 
Address B - the address we sent the letter to previously

For example:

John Smith
15 Madeup Street
Faketown Australia

this is a follow up letter to advise we have sent your previous letter to 33 Fake Place Nowhere Australia.

My conclusion is that I obviously need an Address A section and an Address B section to break the addresses up. Is there a way though - if address a and address b are the same, that address a populates at the bookmarks set for Address B?

eg:

If address' are different:

(bookmarkaddressA) = textboxaddressA
(bookmarkaddressB) = textboxaddressB

If address' are the same:

(bookmarkaddressA) = textboxaddressA
(bookmarkaddressB) = textboxaddressA

Ideally I would like it to function like the "is the postal address the same as the residential address?" checkbox - and just grey out/lock textboxaddressb and fill the info from textboxaddressa

Any suggestions welcome.

full code:

    Option Explicit

Private Sub CheckBox1_Click()
If (CheckBox1.Value = True) Then TextBoxStreet2 = TextBoxStreet
If (CheckBox1.Value = True) Then TextBoxSuburb2 = TextBoxSuburb
If (CheckBox1.Value = True) Then TextBoxPostcode2 = TextBoxpostcode
If (CheckBox1.Value = True) Then ComboBoxState2 = ComboBoxState
If (CheckBox1.Value = False) Then TextBoxStreet2 = Null
If (CheckBox1.Value = False) Then TextBoxSuburb2 = Null
If (CheckBox1.Value = False) Then TextBoxPostcode2 = Null
If (CheckBox1.Value = False) Then ComboBoxState2 = Null
End Sub


Private Sub ComboBoxTitle_Change()

End Sub

Private Sub CommandButtonCancel_Click()
    Unload Me
End Sub

Private Sub CommandButtonClear_Click()
TextBoxFN.Value = Null
TextBoxGN.Value = Null
ComboBoxState.Value = Null
ComboBoxTitle.Value = Null
TextBoxStreet.Value = Null
TextBoxSuburb.Value = Null
TextBoxpostcode.Value = Null
TextBoxCD.Value = Null
TextboxMPN.Value = Null
TextBoxMPDD.Value = Null
TextBoxNPN.Value = Null
TextBoxNPDD.Value = Null
ComboBoxState2.Value = Null
TextBoxStreet2.Value = Null
TextBoxSuburb2.Value = Null
TextBoxPostcode2.Value = Null
CheckBox1.Value = False
End Sub

Private Sub CommandButtonOk_Click()
    Application.ScreenUpdating = False
    With ActiveDocument
        .Bookmarks("Title").Range.Text = ComboBoxTitle.Value
        .Bookmarks("GN").Range.Text = TextBoxGN.Value
        .Bookmarks("FN").Range.Text = TextBoxFN.Value
        .Bookmarks("FN2").Range.Text = TextBoxFN.Value
        .Bookmarks("Street").Range.Text = TextBoxStreet.Value
        .Bookmarks("Suburb").Range.Text = TextBoxSuburb.Value
        .Bookmarks("State").Range.Text = ComboBoxState.Value
        .Bookmarks("PostCode").Range.Text = TextBoxpostcode.Value
        .Bookmarks("Street2").Range.Text = TextBoxStreet2.Value
        .Bookmarks("Suburb2").Range.Text = TextBoxSuburb2.Value
        .Bookmarks("State2").Range.Text = ComboBoxState2.Value
        .Bookmarks("PostCode2").Range.Text = TextBoxPostcode2.Value
        .Bookmarks("CD").Range.Text = TextBoxCD.Value
        .Bookmarks("MPN").Range.Text = TextboxMPN.Value
        .Bookmarks("MPN2").Range.Text = TextboxMPN.Value
        .Bookmarks("MPN3").Range.Text = TextboxMPN.Value
        .Bookmarks("MPN4").Range.Text = TextboxMPN.Value
        .Bookmarks("MPN5").Range.Text = TextboxMPN.Value
        .Bookmarks("MPDD").Range.Text = TextBoxMPDD.Value
        .Bookmarks("NPN").Range.Text = TextBoxNPN.Value
        .Bookmarks("NPDD").Range.Text = TextBoxNPDD.Value
    End With
    Application.ScreenUpdating = True
    Unload Me
End Sub


Private Sub UserForm_Initialize()
    With ComboBoxState
        .AddItem "QLD"
        .AddItem "NSW"
        .AddItem "ACT"
        .AddItem "VIC"
        .AddItem "TAS"
        .AddItem "SA"
        .AddItem "WA"
        .AddItem "NT"
    End With
    With ComboBoxTitle
        .AddItem "Mr"
        .AddItem "Mrs"
        .AddItem "Miss"
        .AddItem "Ms"
    End With
lbl_Exit:
    Exit Sub
End Sub

Private Sub TextBoxMPN_Change()
    TextboxMPN = UCase(TextboxMPN)
End Sub

Private Sub TextBoxNPN_Change()
    TextBoxNPN = UCase(TextBoxNPN)
End Sub

Private Sub TextBoxFN_Change()
    TextBoxFN = UCase(TextBoxFN)
End Sub

If you already have code, it would be better to include that in your post. It's much easier to change existing code then start from scratch. Which part *exactly* of this are you having a problem with ? You know what you need to do, so which part are you unsure about ? — Tim Williams, May 26 '17 at 05:31
Hi Tim, coding multiple text fields isn't the issue im having, I can't find out what code to use to duplicate the information in textboxaddressA to textboxaddressB I would like to have textboxfields for address A and address B - easy enough to do but I also want to add a checkbox "is address A the same as address B?" ticking the checkbox will copy the information from Address A text fields to Address B text fields, which im assuming will look something like: textboxaddressB.value =textboxaddressA.value just don't know how to code the checkbox to automate the copy from A to B — Damon M, Jun 05 '17 at 03:39
https://support.cex.io/hc/en-us/article_attachments/204510638/address.png — Damon M, Jun 05 '17 at 03:46
Typically if there is a "same as A" checkbox I would not to copy the address over to the "address B" fields, but make them inactive (grey them out). Then when you want to get addressB from the form, query the checkbox to determine whether you get the values from the "A" fields or the "B" fields. It would be helpful if you showed the relevant code from your form. — Tim Williams, Jun 05 '17 at 04:15
Thank you for your suggestions - I figured it out in the end - thanks for your help tim :) — Damon M, Jun 05 '17 at 06:40
@TimWilliams full code has been added - curious though, how would you have done it ? could you show me what code you would have used??? — Damon M, Jun 05 '17 at 06:53

score 1 · Accepted Answer · answered Jun 05 '17 at 16:40

1

Since you asked, this is what i might have done (some code not included for clarity):

'disable "address B" controls is user selects to use same address for both
Private Sub CheckBox1_Click()
    Dim en As Boolean
    en = Not CheckBox1.Value
    EnableControls Array(TextBoxStreet2, TextBoxSuburb2, _
                          ComboBoxState2, TextBoxPostcode2), en
End Sub


'utility sub: enable/disable controls
Private Sub EnableControls(cons, bEnable As Boolean)
    Dim con
    For Each con In cons
        With con
            .Enabled = bEnable
            .BackColor = IIf(bEnable, vbWhite, RGB(200, 200, 200))
        End With
    Next con
End Sub


Private Sub CommandButtonOk_Click()
    Dim useAforB As Boolean
    useAforB = CheckBox1.Value

    Application.ScreenUpdating = False
    With ActiveDocument
        '....
        .Bookmarks("Street").Range.Text = TextBoxStreet.Value
        .Bookmarks("Suburb").Range.Text = TextBoxSuburb.Value
        .Bookmarks("State").Range.Text = ComboBoxState.Value
        .Bookmarks("PostCode").Range.Text = TextBoxpostcode.Value

        .Bookmarks("Street2").Range.Text = IIf(useAforB, _
                                     TextBoxStreet.Value, TextBoxStreet2.Value)
        .Bookmarks("Suburb2").Range.Text = IIf(useAforB, _
                                     TextBoxSuburb.Value, TextBoxSuburb2.Value)
        .Bookmarks("State2").Range.Text = IIf(useAforB, _
                                     ComboBoxState.Value, ComboBoxState2.Value)
        .Bookmarks("PostCode2").Range.Text = IIf(useAforB, _
                                     TextBoxpostcode.Value, TextBoxPostcode2.Value)
        '...
    End With
    Application.ScreenUpdating = True
    Unload Me
End Sub

answered Jun 05 '17 at 16:40

Tim Williams

154,628
8
97
125

so that code above didn't grey out the address b fields - it just copied over like I did with the above code - can you please advise if I've missed something or if I am supposed to add or delete something. – Damon M Jun 06 '17 at 02:44
if you can share your workbook I can take a look but I can't debug your code. The code above (the greying out part) worked for me in my testing. – Tim Williams Jun 06 '17 at 03:34
happy to share the workbook - I just don't know how to do that haha. sorry I am very new to all of this. – Damon M Jun 06 '17 at 04:07
You can share from GDrive/Box/DropBox and post a link here. – Tim Williams Jun 06 '17 at 04:14
oop don't worry! I figured it out - I was putting the code in the wrong place. @Tim Williams you are an absolute legend !!!! – Damon M Jun 06 '17 at 04:26
Is there a way to do it in opposite? So for example: Having the address already greyed out as it is assumed they are only at address A - but ticking checkbox would enable Users to enter information in Address B as it is no longer greyed out? – Damon M Jun 06 '17 at 04:48
You just need to change the checkbox label to "Enter a second address" and use `en = CheckBox1.Value` (i.e. remove the `Not`) – Tim Williams Jun 06 '17 at 05:51
perfect! thank you again Tim I really appreciate it - the more I learn about coding the more excited I get to play with it and tweak it ! thanks heaps mate! – Damon M Jun 06 '17 at 05:55
Good luck - it gets easier as you go ! – Tim Williams Jun 06 '17 at 06:01
Hmmm... Maybe not the case - ive copied your code into another form but whenever I try running the userform it stops at (useAforB, _ Compile error - any ideas? – Damon M Jun 06 '17 at 06:34
Don't worry - my dumb a$$ forgot to add a bunch of code hahaha. thanks again tim. – Damon M Jun 06 '17 at 06:42

Microsoft Word VBA multiple addresses - text box

1 Answers1