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
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