设置复选框LinkedCell属性时触发Run-time Error '438'问题

错误原因 #

报错根源是你直接通过Shape对象调用LinkedCell属性，但Shape对象本身不支持该属性。通过AddFormControl创建的表单控件，必须通过Shape.ControlFormat对象才能访问其专属属性（比如LinkedCell）。

修复后的代码 #

Sub AddCheckboxes()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim checkColumn As Integer
    Dim valueColumn As Integer
    Dim i As Long
    Dim shp As Shape

    Set ws = ThisWorkbook.Sheets(1)
    Set rng = ws.Range("B2:B100")

    checkColumn = 24
    valueColumn = 2

    ' 删除现有形状
    On Error Resume Next
    For i = ws.Shapes.Count To 1 Step -1
        ws.Shapes(i).Delete
    Next i
    On Error GoTo 0
    ws.Columns(checkColumn).ClearContents

    For Each cell In rng
        If IsNumeric(cell.Value) And cell.Value > 0 Then
            Set shp = ws.Shapes.AddFormControl(xlCheckBox, _
                                          ws.Cells(cell.Row, checkColumn).Left + 5, _
                                          ws.Cells(cell.Row, checkColumn).Top, 15, 15)
            ' 通过ControlFormat访问表单控件的LinkedCell属性
            shp.ControlFormat.LinkedCell = ws.Cells(cell.Row, checkColumn).Address
        End If
    Next cell
End Sub

关键修改说明 #

• 将原来直接对Shape对象设置.LinkedCell的代码，改为shp.ControlFormat.LinkedCell，通过ControlFormat对象操作表单控件的绑定单元格属性。
• 新增Set shp = ...将创建的Shape对象赋值给变量，让代码逻辑更清晰，也方便后续对该复选框进行其他属性设置。

内容的提问来源于stack exchange，提问作者user25656993