如何在Excel VBA 2010中的IF-THEN语句中使用复选框？ [英] How do I use checkboxes in an IF-THEN statement in Excel VBA 2010?

问题描述

我需要使用IF-THEN语句复选框的值。根据用户检查的方式，我必须计算事情的变化。但是，我不知道如何使用复选框值，或者如何检测它们。这是我迄今为止的代码：

  Private Sub Workbook_Open（）
范围（E1：F7，A1 ：A4，B1：B4，C1：C3）。选择
 With Selection.Borders（xlEdgeLeft）
 .LineStyle = xlContinuous 
 .ColorIndex = 0 
 .TintAndShade = 0 
 .Weight = xlThin 
 End with 
 With Selection.Borders（xlEdgeTop）
 .LineStyle = xlContinuous 
 .ColorIndex = 0 
 .TintAndShade = 0 
 .Weight = xlThin 
 End with 
 With Selection.Borders（xlEdgeBottom）
 .LineStyle = xlContinuous 
 .ColorIndex = 0 
 .TintAndShade = 0 
 .Weight = xlThin 
 End with 
 With Selection.Borders（xlEdgeRight）
 .LineStyle = xlContinuous 
 .ColorIndex = 0 
 .TintAndShade = 0 
 .Weight = xlThin 
 End with 
 With Selection.Borders（xlInsideVertical）
 .LineStyle = xlContinuous 
 .ColorIndex = 0 
 .TintAndShade = 0 
 .Weight = xlThin 
 End with 
 With Selection.Borders（xlInsideHorizo​​ntal）
 .LineStyle = xlContinuous 
。 ColorIndex = 0 
 .TintAndShade = 0 
 .Weight = xlThin 
结束
范围（A1）。选择
范围（A1）=时间
范围（B1）=样本形状
范围（C1）=数据类型
范围（A1：C1）。Font.Bold = True 
范围（E1）=所有者
范围（E2）=实验日期
范围（E3）=样本ID
范围E4）=污染物
范围（E5）=Leachant
范围（E6）=温度
范围（E7）=回归标题 
范围（E1：E7）Font.Bold = True 
列（A：E）。EntireColumn.EntireColumn.Autofit 
'格式化列A 
列A）。EntireColumn.ColumnWidth = 9.71 
 ActiveSheet.CheckBoxes.Add（4，14.5,72,17.25）。选择
 Selection.Ch aracters.Text =Days
 Range（A6）。选择
 ActiveSheet.CheckBoxes.Add（4，30.5,73.5,17.25）。选择
 Selection.Characters.Text =小时
 ActiveSheet.CheckBoxes.Add（4，45.75,52.5,17.25）。选择
 Selection.Characters.Text =Minutes
'格式化列B 
 ActiveSheet.CheckBoxes 。添加（58，14.5，72，17.25）。选择
 Selection.Characters.Text =Cylinder
 ActiveSheet.CheckBoxes.Add（58，30.5,73.5,17.25）。选择
 Selection.Characters.Text =Wafer
 ActiveSheet.CheckBoxes.Add（58，45.75,52.5,17.25）。选择
 Selection.Characters.Text =不规则
'格式化列C 
列（C）。EntireColumn.ColumnWidth = 12.71 
 ActiveSheet.CheckBoxes.Add（140.5,14.5,72,17.25）。选择
 Selection.Characters.Text =Incremental
 ActiveSheet.CheckBoxes.Add（140.5，30.5，72，17.25）。选择
 Selection.Characters.Text =累积
列（F）。EntireColum n.ColumnWidth = 60 
范围（A1：C1）。Horizo​​ntalAlignment = xlCenter 
范围（F1）。选择
 Dim btn As Button 
 Dim rng As Range 
与工作表（Sheet1）
设置rng = .Range（A9：C9）
设置btn = .Buttons.Add（rng.Left，rng.Top，rng.Width， rng.Height）
带btn 
 .Caption =上面做出选择后，点击这个按钮继续。 
 .AutoSize = True 
 .OnAction =DataInput
结束
结束
结束Sub 
  

我想要做的，就像测试一样，如果勾选时间复选框，然后按下按钮继续，我想要它说YAY，使用IF-THEN语句。如果没有选中时间复选框，然后按继续，我想让它说AWW ...。

这是我试图做的这是发生的，它不工作。

  Sub DataInput（）
如果ActiveSheet.Shapes.Range（Array （复选框1））值= True然后
 MsgBox（Yay）
否则：MsgBox（Aww）
如果
 End Sub 
  

我做错了什么？

解决方案

  If Sheets（Sheet1）。OLEObjects（CheckBox1）。Object.Value = True Then 
  pre> 
 
 
我相信Tim是对的你有一个表单控件。为此，您必须使用这个
 如果ActiveSheet.Shapes（复选框1）。ControlFormat.Value = 1 Then 
  
 
I need to use the value of checkboxes for an IF-THEN statement. Based on what the user checks, the way I have to calculate things changes. However, I can't figure out how to use the checkbox values, or how to detect them. Here is the code I have so far:
Private Sub Workbook_Open()
    Range("E1:F7,A1:A4,B1:B4,C1:C3").Select
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("A1").Select
    Range("A1") = "Time"
    Range("B1") = "Specimen Shape"
    Range("C1") = "Data Type"
    Range("A1:C1").Font.Bold = True
    Range("E1") = "Owner"
    Range("E2") = "Experiment Date"
    Range("E3") = "Specimen ID"
    Range("E4") = "Contaminant"
    Range("E5") = "Leachant"
    Range("E6") = "Temperature"
    Range("E7") = "Regression Title"
    Range("E1:E7").Font.Bold = True
    Columns("A:E").EntireColumn.EntireColumn.Autofit
    'Formatting Column A
    Columns("A").EntireColumn.ColumnWidth = 9.71
    ActiveSheet.CheckBoxes.Add(4, 14.5, 72, 17.25).Select
    Selection.Characters.Text = "Days"
    Range("A6").Select
    ActiveSheet.CheckBoxes.Add(4, 30.5, 73.5, 17.25).Select
    Selection.Characters.Text = "Hours"
    ActiveSheet.CheckBoxes.Add(4, 45.75, 52.5, 17.25).Select
    Selection.Characters.Text = "Minutes"
    'Formatting Column B
    ActiveSheet.CheckBoxes.Add(58, 14.5, 72, 17.25).Select
    Selection.Characters.Text = "Cylinder"
    ActiveSheet.CheckBoxes.Add(58, 30.5, 73.5, 17.25).Select
    Selection.Characters.Text = "Wafer"
    ActiveSheet.CheckBoxes.Add(58, 45.75, 52.5, 17.25).Select
    Selection.Characters.Text = "Irregular"
    'Formatting Column C
    Columns("C").EntireColumn.ColumnWidth = 12.71
    ActiveSheet.CheckBoxes.Add(140.5, 14.5, 72, 17.25).Select
    Selection.Characters.Text = "Incremental"
    ActiveSheet.CheckBoxes.Add(140.5, 30.5, 72, 17.25).Select
    Selection.Characters.Text = "Cumulative"
    Columns("F").EntireColumn.ColumnWidth = 60
    Range("A1:C1").HorizontalAlignment = xlCenter
    Range("F1").Select
    Dim btn As Button
    Dim rng As Range
    With Worksheets("Sheet1")
        Set rng = .Range("A9:C9")
            Set btn = .Buttons.Add(rng.Left, rng.Top, rng.Width, rng.Height)
        With btn
            .Caption = "After making your selections above, click this button to continue."
            .AutoSize = True
            .OnAction = "DataInput"
        End With
    End With
End Sub
What I want it to do, just as a test, is if the "Time" checkbox is checked, and then the button is pressed to continue, I want it to say something like "YAY", using an IF-THEN statement. If the "Time" checkbox is not checked and you press continue, I'd like it to say "AWW...".

This is what I tried to make that happen, and it isn't working.
Sub DataInput()
    If ActiveSheet.Shapes.Range(Array("Check Box 1")).Value = True Then
    MsgBox ("Yay")
    Else: MsgBox ("Aww")
    End If
End Sub
What am I doing wrong?
解决方案
If Sheets("Sheet1").OLEObjects("CheckBox1").Object.Value = True Then
I believe Tim is right. You have a Form Control. For that you have to use this
If ActiveSheet.Shapes("Check Box 1").ControlFormat.Value = 1 Then


                        
这篇关于如何在Excel VBA 2010中的IF-THEN语句中使用复选框？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！