VBA & Macro Problems

How to Use a VBA Macro to Add Borders and Column Totals to a Selected Range

Muhammad Talha Sep 28, 2026 869 views

Question details

The user needs a VBA macro script that allows selecting a cell range, applies thin borders to columns A through C, and automatically inserts a row to calculate the sum for columns B and C.

How to Create a VBA Macro to Add Borders and Totals to a Selected Range

Product: Spreadsheet
Device & OS: not provided
Scenario: Automating the process of formatting data by generating borders and summing up specific columns dynamically based on a user's manual selection.
Observed behavior: The user is looking for the correct VBA code sequence using Application.InputBox to define the target range, format it, and insert mathematical formulas seamlessly.

Before you start

Before running any new VBA macros, ensure you have enabled Developer Tools in your spreadsheet application and save a backup copy of your workbook to prevent accidental data overwriting.

Solution 1Recommended

Create a VBA Macro with Application.InputBox

Write a custom VBA script using Application.InputBox to capture the target range, apply border styles, and dynamically insert SUM formulas.

This solution utilizes the 'Application.InputBox' method with 'Type:=8', which allows the user to visually highlight a range with their mouse. The code then uses the 'Intersect' function to restrict formatting exclusively to columns A, B, and C, ensuring that neighboring columns remain unaffected.

Open the Visual Basic Editor

Navigate to the 'Developer' tab in your spreadsheet application and click on 'Visual Basic', or use the keyboard shortcut Alt + F11 to open the editor window.

Insert a New Module

In the Project window on the left, right-click on your workbook name, select 'Insert', and choose 'Module'. This will create a blank text area for your code.

Write the Macro Code

Paste your macro code into the module. Use 'Set myRange = Application.InputBox("Select Range", Type:=8)' to prompt the user. Then, use 'myRange.Borders.LineStyle = xlContinuous' for borders, and define the last row offset to insert '.Formula = "=SUM(...)"' for columns B and C.

Run the Macro

Close the VBA editor and return to your worksheet. Press Alt + F8 to open the Macro dialog, select your newly created macro, and click 'Run'. When the prompt appears, highlight your target data range and click OK.

Create a VBA Macro with Application.InputBox

Error Handling: It is highly recommended to include 'On Error Resume Next' before the InputBox prompt. If a user clicks 'Cancel' on the prompt, it will throw a Type Mismatch error unless handled properly.

Efficient Data Processing with WPS Spreadsheet

Automate Your Worksheets with WPS Office

WPS Spreadsheet features robust data formatting tools and supports VBA macros, making it incredibly simple to automate repetitive tasks like applying borders and inserting column totals.

1. Open your workbook: Launch WPS Spreadsheet and open the document you wish to automate.
2. Access Developer Tools: Click on the 'Developer' tab on the top ribbon to access the Macro and Visual Basic editors.
3. Implement the Macro: Write or paste your custom VBA script directly into the WPS Visual Basic environment and execute it.
4. Save as Macro-Enabled: Save your document as an Excel Macro-Enabled Workbook (.xlsm) to ensure your automation scripts are preserved.

Seamless compatibility with Microsoft Excel (.xlsx and .xlsm) formats.Built-in Developer tab for easy access to the Visual Basic environment.Lightweight, fast, and capable of handling complex automated scripts.Familiar spreadsheet interface that requires zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

How do I prompt a user to select a range in a VBA macro?

You can use 'Application.InputBox' and set the parameter 'Type:=8'. This forces the input box to expect a cell reference, allowing the user to highlight a range of cells directly on the worksheet using their mouse.

How do I apply continuous borders only to specific columns in my selection?

You can use the 'Intersect' method in VBA to overlap the user's selection with the desired columns. For example: 'Intersect(SelectedRange, Columns("A:C")).Borders.LineStyle = xlContinuous' ensures borders only appear in columns A through C within the highlighted area.

Why does my macro crash when I click Cancel on the selection prompt?

When 'Type:=8' is used, clicking Cancel returns a Boolean value (False) instead of a Range object, causing a 'Type Mismatch' error. You can prevent this by adding 'On Error Resume Next' before the InputBox line and 'On Error GoTo 0' after it, then checking if the range variable is 'Nothing' before proceeding.

Excel & Data Problems