Trending checkboxes?

[Tre_cool]

Hi,
I have two sheets, one's a check sheet with 11 check boxes in it an
the other is for trending the results each time the checksheet is used
I want to copy over the results of the check sheet (i.e if the checkbo
is checked or not) over to the other page by adding true or false fo
each question on the trending sheet.

I was trying to loop through the checkboxes but I couldn't get that t
work.

Can someone please help me?

Thanks in advance
Trevo

 

[Tom Ogilvy]

if the are from the forms toolbar

for each c in worksheets("sheet2").Checkboxes

if from the control toolbox toolbar

Dim cBox as MSForms.CheckBox
Dim obj as OleObject
for each obj in Worksheets("Sheet2").OleObjects
if Typeof Obj.Object is MSForms.Checkbox then
set cBox = Obj.Object
msgbox cBox.Name & " - " & cBox.Value
end if
Next

If you have use names like cb1, cb2
Dim cb as MsForms.Checkbox
for i = 1 to 11
set cb = Activesheet.OleObjects("cb" & i).Object
msgbox cb.Name & " - " & cb.Value
Next

 

[Leith Ross]

Hello Trevor,

Here is macro for the Forms type Check Box. You can modify this easily
to fit your code. There are three things you need to change. The name
of the Trend worksheet, in this example it is "Sheet2". You will need
to change the cell addresses where the Check Box state will be stored.
Here it is set for "C1", "C2", "C3". Lastly, you will need to add mose
Case statements to match the captions of the Check Boxes.

After you have made the changes and saved the code into a VBA module,
you can assign the macro to your Check Boxes. When they change so will
the result in the answer cell to either TRUE or FALSE.


Code:
--------------------
Public Sub GetCheckBoxState()

Dim Check_Box As Object
Dim Check_Box_Name As String
Dim CurrentState As Boolean
Dim TrendWks As Worksheet

Set Check_Box = ActiveSheet.Shapes(Application.Caller)
Set TrendWks = Worksheets("Sheet2")

If Check_Box.Type <> msoFormControl Then Exit Sub

If Check_Box.FormControlType = xlCheckBox Then
Check_Box_Name = Check_Box.TextFrame.Characters.Text
Select Case Check_Box_Name
Case Is = "Answer 1"
GoSub ReturnState: TrendWks.Range("C1").Value = CurrentState
Case Is = "Answer 2"
GoSub ReturnState: TrendWks.Range("C2").Value = CurrentState
Case Is = "Answer 3"
GoSub ReturnState: TrendWks.Range("C3").Value = CurrentState
End Select
End If

Exit Sub

ReturnState:
CurrentState = False
If Check_Box.ControlFormat.Value = 1 Then CurrentState = True
Return

End Sub

 

[Tre_cool]

Thanks guys...that worked great!