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屋!