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


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=507852