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.
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
- Click on cell E4, to add a check box
- Change the check box label to Same as Ship To
Link the Check Box
- Add a new sheet, and name it, Admin
- In cell B1, type a label: Same as Ship To
- 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
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
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
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.
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.
Excel Check Box Fills in Billing Address
Excel Check Box Fills in Billing Address is a post from Contextures Blog and is not allowed to be copied to other sites