How do I use checkboxes in an IF-THEN statement in Excel VBA 2010

Question

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

User · Answer

A checkbox has a linked cell  which contains the True False representing the state of the checkbox  It is much easier to reference this cell s value than the value of the embedded object which is the checkbox    Manually  Right click on the checkbox  choose Format  click in the Linked Cell box  and select the cell to contain the checkbox value   In code   Set cbTime   ActiveSheet CheckBoxes Add 100  100  50  15  With cbTime      Value   xlOff     unchecked  xlOn   checked      LinkedCell     A 1  End With

User · Answer

Sub Datainput    Checkbox values are 0  false   1  true   2  changed or grey      If activesheet CheckBoxes  Check Box 1   value   1 then         Msgbox   Yay           Else  Msgbox  Aww       End if End Sub

User · Answer

You can try something like this      Dim cbTime  Set cbTime   ActiveSheet CheckBoxes Add 100  100  50  15  With cbTime      Name    cbTime       Characters Text    Time  End With  If ActiveSheet CheckBoxes  cbTime   Value   1 Then  or just cbTime Value      checked Else      unchecked End If

User · Answer

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

User · Answer

It seems that in VBA macro code for an ActiveX checkbox control you use  If  ActiveSheet OLEObjects  CheckBox1   Object Value   True   and for a Form checkbox control you use  If  ActiveSheet Shapes  CheckBox1   OLEFormat Object Value   1

User · Answer

I found that I could access the checkbox directly using Worksheets  SheetName   CB Checkboxname value directly without relating to additional objects

[vba] How do I use checkboxes in an IF-THEN statement in Excel VBA 2010?

Examples related to vba

Examples related to excel

Examples related to excel-2010