Excel编程VBA图形Shape复选框的状态判断

Shape

https://docs.microsoft.com/zh-tw/office/vba/api/excel.shape

设置图形属性

ActiveSheet.Shapes(2).Line.Weight '当前工作表里图形（Shapes）集合里的第二个对象里的直线（Line）对象的粗细（Weight）属性

表单控件

根据图形的 Type 属性判断图形类型，如果是表单控件msoFormControl 则接着判断表单控件的类型FormControlType 基本后通过控件的 ControlFormat.Value 取得控件值

参考

For Each s In Worksheets(1).Shapes
If s.Type = msoFormControl Then
If s.FormControlType = xlCheckBox Then _
s.ControlFormat.Value = False
End If
Next

CheckBox

获取选中状态

选中时Value值为1，未选中时返回 -4146，直接赋值给 bool 会全部转为 True

Dim ckb As Shape
Dim status As Boolean
Set ckb = ActiveSheet.Shapes(Application.Caller)
If ckb.Type = msoFormControl Then
If ckb.FormControlType = xlCheckBox Then
Debug.Print ckb.ControlFormat.Value
If ckb.ControlFormat.Value = 1 Then
status = True
Else
status = False
End If
End If
End If
Debug.Print status

实例

根据复选框的状态添加删除线到当前行

Sub BtnComplete()
Dim curRow As Integer
Dim ckb As Shape
Dim status As Boolean
Set ckb = ActiveSheet.Shapes(Application.Caller)
If ckb.Type = msoFormControl Then
If ckb.FormControlType = xlCheckBox Then
Debug.Print ckb.ControlFormat.Value
If ckb.ControlFormat.Value = 1 Then
status = True
Else
status = False
End If
End If
End If
Debug.Print status
curRow = ActiveSheet.Shapes(ckb.Name).TopLeftCell.Row
With Range("A" & curRow & "😄" & curRow).Font
.Strikethrough = status
End With
End Sub