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
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
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With