Search code examples
vbams-wordstreet-address

Microsoft Word VBA multiple addresses - text box


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

Solution

  • 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