Excel Check Box Fills in Billing Address

Written by Debra Dalgleish from Contextures Blog

There’s an Excel Order Form tutorial on my Contextures site, with written steps and a video that show how to set it up from scratch. I’ve just added a new section, with a check box that fills in the billing address. Add a check mark, and a macro copies the shipping address for you.

Billing Address Check Box

Here’s the new version of the Excel Order Form, with a Bill To section, to the right of the Ship To section. The “Same as Ship To” check box runs a small macro.

To use the same address, click the check box, to add a check mark. The macro copies the shipping address to the Bill To section.
To clear the billing address, click the check box again, to remove the check mark.

Test the macro and make final adjustments

Build an Order Form

Here’s the video that shows how to build an order form. The billing check box is based on this completed order form. You can download the “get started” file or the completed order form from my Contextures site.

Add the Billing Check Box

This is a quick list of the steps for adding a check box that fills in the billing address for you. The full details, and the completed Excel file are on the Order Form page of my Contextures site.

NOTE: The check box runs a macro, so save your workbook as macro-enabled, before you follow these steps.

On the Developer tab, click the Check Box tool in Form Controls

insert a check box (form control)

Click on cell E4, to add a check box

add a check box

Change the check box label to Same as Ship To

change check box text

Link the Check Box

Add a new sheet, and name it, Admin
In cell B1, type a label: Same as Ship To

formatted cell and label

On the Order Form sheet, right-click on the check box, and click Format Control
Link the check box to cell A1 on the Admin sheet

cell link added

Name the Cell Ranges

Next, you’ll create 3 named ranges — for the Ship To and Bill To cells, and the linked cell on the Admin sheet. These names will be used in the check box macro.

On the Order Form sheet, select cells B5:C7, and name that range as ShipTo
Select cells D5:E7, and name that range, BillTo
On the Admin sheet, select cell A1, and name it, BillLink

named range ShipTo

Add the Macro

Copy the code for the ChangeBillAddress macro (below), and paste it into a regular code module in the Order Form workbook.

Here’s how the macro works:

If cell A1 on the Admin sheet (BillLink) contains TRUE,
- copy shipping address (ShipTo) to the billing section (BillTo)
If the cell contains FALSE,
- clear the contents of the billing section.

Sub ChangeBillAddress()
Dim wsDE As Worksheet
Dim wsA As Worksheet
Dim rngBill As Range
Dim rngShip As Range
Dim rngLink As Range

Set wsA = Sheets("Admin")
Set wsDE = Sheets("Order Form")
Set rngBill = wsDE.Range("BillTo")
Set rngShip = wsDE.Range("ShipTo")
Set rngLink = wsA.Range("BillLink")

If rngLink = True Then
  rngBill.Value = rngShip.Value
Else
  rngBill.ClearContents
End If
End Sub

Assign the Macro

Finally, follow these steps, to assign the new macro to the check box:

Right-click the check box, and click Assign Macro
In the list of macros, click on ChangeBillAddress, then click OK

assign the new macro

Test the Macro

Before you test the macro, save your workbook, just in case something goes wrong

Click on the Order Form sheet, away from the check box, to unselect the check box
Click the check box, to add a check mark, and the shipping address should be copied to the billing section.
Click the check box, to remove the check mark, and the billing section contents should be cleared.

Test the macro and make final adjustments

Get the Order Form Check Box File

Get the full setup details, and the completed Excel file on the Order Form page of my Contextures site. The zipped workbook is in xlsm format, and contains macros. Be sure to enable macros when you open the workbook, if you want to test the check box.

________________________