Writing a Macro

[Britt]

I am creating a form in Microsoft Word 2003. There is an area that requests a
physical company information, including;
Address:
City, State, Zip:
Phone:
Fax:
Primary Contact:
Email:

I would like to make it so that when a box labeled 'same as physical
address' is checked that the information that has been entered will autofill
to corresponding fields labeled Billing Information, with the following
fields:
Address:
City, State, Zip:
Phone:
Fax:
Primary Contact:
Email:

Can anyone help me with this?

 

[Doug Robbins - Word MVP]

See the Repeating Data page of Greg Maxey's website at:

http://gregmaxey.mvps.org/Repeating_Data.htm

--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my services on
a paid professional basis.

 

[Jay Freedman]

Doug, I don't think any of the techniques on Greg's page will apply
here, since the Billing fields should operate independently when the
check box is unchecked.

Instead, I'd suggest writing an exit macro for the check box that
looks at the state of the check box and takes one of two sets of
actions:

- If the box is checked, copy the content of each "physical address"
field to the corresponding "billing address" field, and set Enabled =
False for each billing field so it can't be edited.

- If the box is unchecked, set Enabled = True for each "billing
address" field. (That way, if the user changes the box from checked to
unchecked, the billing fields can be edited again.)

The macro would look something like this:

Sub ExitCheck()
With ActiveDocument.FormFields
If .Item("CheckSame").CheckBox.Value = True Then
.Item("BillAddr").Result = .Item("PhysAddr").Result
.Item("BillAddr").Enabled = False
' repeat these two lines for other fields
Else
.Item("BillAddr").Enabled = True
' repeat this line for other fields
End If
End With
End Sub

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[Gordon Bentley-Mix]

I'd say you're right Jay. This is similar to what I do in a UserForm that
has a similar requirement. In my case, I have a Registered Address and a
Service Address, and there's a checkbox to indicate that the Service Address
is the same as the Registered Address. If the checkbox is selected, then I
want to copy the values from the Registered Address details into the Service
Address details and disable the Service Address details so they can't be
modified. And if the checkbox is subsequently cleared, I want to 'reset' and
re-enable the Service Address details to allow direct entry. My code looks
like this:

Private Sub chkServiceSameAsRegistered_Change()
If chkServiceSameAsRegistered.Value = True Then
CopyRegisteredAddressDetailsToServiceAddressDetails Else
ClearServiceAddressDetails
End Sub

Private Sub CopyRegisteredAddressDetailsToServiceAddressDetails()
With txtServiceAddressL1
.Locked = True
.TabStop = False
.BackColor = &H8000000F
.Value = txtRegisteredAddressL1.Value
End With
With txtServiceAddressL2
.Locked = True
.TabStop = False
.BackColor = &H8000000F
.Value = txtRegisteredAddressL2.Value
End With
With txtServiceAddressCity
.Locked = True
.TabStop = False
.BackColor = &H8000000F
.Value = txtRegisteredAddressCity.Value
End With
With txtServiceAddressPostCode
.Locked = True
.TabStop = False
.BackColor = &H8000000F
.Value = txtRegisteredAddressPostCode.Value
End With
End Sub

Private Sub ClearServiceAddressDetails()
With txtServiceAddressL1
.Locked = False
.TabStop = True
.BackColor = &H80000005
If .Value = txtRegisteredAddressL1.Value Then .Value = ""
End With
With txtServiceAddressL2
.Locked = False
.TabStop = True
.BackColor = &H80000005
If .Value = txtRegisteredAddressL2.Value Then .Value = ""
End With
With txtServiceAddressCity
.Locked = False
.TabStop = True
.BackColor = &H80000005
If .Value = txtRegisteredAddressCity.Value Then .Value = ""
End With
With txtServiceAddressPostCode
.Locked = False
.TabStop = True
.BackColor = &H80000005
If .Value = txtRegisteredAddressPostCode.Value Then .Value = ""
End With
End Sub

(It's maybe a bit more 'modular' than would be strictly necessary in most
applications, but I've done it this to meet certain other requirements that
most people don't have.)

Then on the document side, I just insert whatever is in the various
textboxes on the UF into corresponding bookmarks - not that it really
matters for "Britt". The point is that my approach is very similar to what
you're recommending for use with form fields. The one thing that might be
missing from your approach is a step to 'reset' the Billing Address form
fields if the user goes back and clears the checkbox again. Something like:

.Item("BillAddr").Enabled = True '<--- from Jay's code
.Item("BillAddr").Result = "" '<--- add this line for each form field

should do the trick.

 

[Jay Freedman]

Whether Britt should add lines to clear the billing address fields
when the box becomes unchecked is a design decision that needs some
consideration. It depends on which is likely to be the more common
situation:

- The user checks the box, then decides to make a small change in one
of the billing fields, and unchecks the box. It would be annoying if
all the fields are instantly cleared and need to be refilled manually.

- The user checks the box, then realizes the mistake and needs to
enter completely different information in all the fields. In this case
clearing all the fields would be helpful.

--
Jay

I'd say you're right Jay. This is similar to what I do in a UserForm that
has a similar requirement. In my case, I have a Registered Address and a
Service Address, and there's a checkbox to indicate that the Service Address
is the same as the Registered Address. If the checkbox is selected, then I
want to copy the values from the Registered Address details into the Service
Address details and disable the Service Address details so they can't be
modified. And if the checkbox is subsequently cleared, I want to 'reset' and
re-enable the Service Address details to allow direct entry. My code looks
like this:
[snip code]

(It's maybe a bit more 'modular' than would be strictly necessary in most
applications, but I've done it this to meet certain other requirements that
most people don't have.)

Then on the document side, I just insert whatever is in the various
textboxes on the UF into corresponding bookmarks - not that it really
matters for "Britt". The point is that my approach is very similar to what
you're recommending for use with form fields. The one thing that might be
missing from your approach is a step to 'reset' the Billing Address form
fields if the user goes back and clears the checkbox again. Something like:

.Item("BillAddr").Enabled = True '<--- from Jay's code
.Item("BillAddr").Result = "" '<--- add this line for each form field

should do the trick.
--
Cheers!

Gordon Bentley-Mix

Doug, I don't think any of the techniques on Greg's page will apply
here, since the Billing fields should operate independently when the
check box is unchecked.

Instead, I'd suggest writing an exit macro for the check box that
looks at the state of the check box and takes one of two sets of
actions:

- If the box is checked, copy the content of each "physical address"
field to the corresponding "billing address" field, and set Enabled =
False for each billing field so it can't be edited.

- If the box is unchecked, set Enabled = True for each "billing
address" field. (That way, if the user changes the box from checked to
unchecked, the billing fields can be edited again.)

The macro would look something like this:

Sub ExitCheck()
With ActiveDocument.FormFields
If .Item("CheckSame").CheckBox.Value = True Then
.Item("BillAddr").Result = .Item("PhysAddr").Result
.Item("BillAddr").Enabled = False
' repeat these two lines for other fields
Else
.Item("BillAddr").Enabled = True
' repeat this line for other fields
End If
End With
End Sub

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.