VBA & Macro Problems

Change an Excel Option Button's Linked Cell with VBA

Emma BrownEmma Brown Nov 22, 2026 868 views

Question details

Update option button output so each selection is recorded in the correct row as a VBA data-entry form moves to a new record.

Change an Excel Option Button's Linked Cell with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
A data-entry form writes records to successive rows on another worksheet.
Observed behavior
The option buttons need to point to cells in the current record row rather than rely on whichever cell is active.
Before you start

Identify whether the buttons are worksheet Form controls, worksheet ActiveX controls, or controls on a VBA UserForm. Confirm the worksheet and control names, and choose a column that will be populated for every saved record.

Solution 1Recommended

Update the linked cell of worksheet Form controls

Use this method for a group of Form control option buttons on a worksheet.

The example assumes the controls are on a sheet named Form, records are on a sheet named Records, and column A contains a value for every saved record. Row 1 contains headers. Change these names and columns to match your workbook.

Form control option buttons in the same group share a linked cell. Excel writes the selected button's index to that cell.

1
Check the control names

On the Form sheet, open Home > Find & Select > Selection Pane and identify the option button names. The example uses Option Button 1 and Option Button 2.

2
Add the VBA procedure

Open Developer > Visual Basic, choose Insert > Module, and add: Sub LinkFormButtons() Dim recordSheet As Worksheet Dim formSheet As Worksheet Dim nextRow As Long Dim target As String Set recordSheet = ThisWorkbook.Worksheets("Records") Set formSheet = ThisWorkbook.Worksheets("Form") nextRow = Application.Max(2, recordSheet.Cells(recordSheet.Rows.Count, "A").End(xlUp).Row + 1) target = "'Records'!$B$" & nextRow formSheet.Shapes("Option Button 1").ControlFormat.LinkedCell = target formSheet.Shapes("Option Button 2").ControlFormat.LinkedCell = target End Sub

3
Link before entering the record

Run LinkFormButtons when preparing a new record, before the user makes a selection. Save the record's column A value in that same row so the next calculation advances to the following row.

4
Test the result

Select an option button on the Form sheet and check column B on the Records sheet. Repeat with another record to confirm that the output moves to the next row.

Update the linked cell of worksheet Form controls
Use a stable record column: Avoid ActiveCell.Offset: the active cell may be unrelated to the record. The last-row calculation depends on column A being filled for each saved record.
Free Microsoft Office alternative

Work with Excel spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface for opening and editing Excel files. It can help you move everyday spreadsheet work across, but it does not fix Excel's VBA or ActiveX behavior; test any macros and controls before relying on them.

Open and edit Microsoft Excel XLS and XLSX filesWork with data-entry tables and record rows in WPS SpreadsheetsUse a familiar interface for everyday spreadsheet editingMove existing spreadsheet files into a lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why shouldn't I use ActiveCell.Offset to find the next record?

ActiveCell refers to the user's current selection, which may be on the wrong row or sheet. Calculate the next row from a designated record column instead, such as the last populated cell in Records column A.

How do I know whether an option button is a Form control or an ActiveX control?

Right-click the button on the worksheet. A Form control offers Format Control, while an ActiveX control exposes Properties when Developer > Design Mode is enabled. UserForm controls are edited in the Visual Basic Editor.

What does a Form control option button write to its linked cell?

A group of Form control option buttons writes the selected button's index to its shared linked cell. Check the cell after selecting each option to verify the mapping.

What if the next-row calculation returns the wrong row?

Check that the column used in Cells(Rows.Count, "A").End(xlUp) is filled for every saved record and does not contain unrelated data below the table. Adjust the sheet, column, and starting row in the VBA procedure to match your layout.

Can I use the same LinkedCell code for a UserForm option button?

No. The worksheet examples use Form control or ActiveX worksheet objects. For a UserForm, read the option button's Value in the form's save event and write it to the intended worksheet row.