Excel VBA表单复选框 [英] Excel VBA Form Checkbox

问题描述

嘿，我是新的Excel VBA，需要别人的帮助。以下是我正在寻找的：

Hey I am new to Excel VBA and need someone's help. Following is what I am looking for:

如果某个未链接（表单）复选框（topleftcell？）被选中，然后
下面的某些复选框）将被检查（如果它们还没有）

If a certain unlinked (form) checkbox(topleftcell?) is checked Then certain checkboxes below it(.offset?) will be checked off (if they are not already)

我不能使用单元格名称，因为上面的同样的代码将适用于大量的列。

I can't use cell names since the same code above will apply to bunch of columns.

这是我有种类的

Set aaa = ActiveSheet.CheckBoxes(Application.Caller) 
With aaa.TopLeftCell 
    If aaa.Value = 1 Then 
        rsp = MsgBox("Check boxes below?", 4)
        If rsp = vbYes Then
             certain checkboxes(.offset?) below will be unchecked &
             .offset(0,0).value= "na"

推荐答案

我们假设您的复选框放置如下图所示。我故意没有对齐它们。

Let's say that your checkboxes are placed as shown in the image below. I deliberately didn't align them.

确定这里是一个小技巧，我们将使用。右键单击第1列中的所有复选框，然后单击格式控制。接下来转到 Alt Text 选项卡，然后键入 1 。对于列2复选框，在 Alt文本中键入 2 。对所有15列重复此操作。

Ok here is a small trick that we will use. Right click on all checkboxes in column 1 one by one and click on Format Control. Next go to the Alt Text tab and type 1. For Column 2 checkboxes, type 2 in the Alt text. Repeat this for all 15 columns.

现在将以下宏分配给所有顶部的复选框。

Now assign the below macro to all the top checkboxes.

Sub CheckBox_Click()
    Dim shp As Shape, MainShp As Shape
    Dim Alttext As String

    '~~> Get the checkbox which called the macro
    Set MainShp = ActiveSheet.Shapes(Application.Caller)
    '~~> Get it's alternate text
    Alttext = MainShp.AlternativeText

    '~~> Loop through all checkboxes except the checkbox which called it
    '~~> and check for their alt text
    For Each shp In ActiveSheet.Shapes
        If shp.Name <> MainShp.Name And shp.AlternativeText = Alttext Then
            '~~> If the top checkbox is checked then set the rest as checked
            If MainShp.OLEFormat.Object.Value = 1 Then
                shp.OLEFormat.Object.Value = 1
            Else
                shp.OLEFormat.Object.Value = 0
            End If
        End If
    Next
End Sub

现在当你点击最顶端的复选框，它会检查所有的复选框下面有相同的 Alt text 作为顶部复选框。

Now when you click the top most checkbox then it will check all the checkboxes below which have the same Alt text as the top checkbox.

这篇关于Excel VBA表单复选框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！