Checkbox detection in macro
 

The shape variable "cantillation"

In your code it's a String variable

cbxValue = ActiveSheet.Shapes(Application.Caller).ControlForm at.Value
or
cbxValue = ActiveSheet.CheckBoxes(Application.Caller).Value

Adapt one of the above.

It might be worth using different macros for different potential caller
types

Regards,
Peter T

"Marvin" wrote in message
...
The shape variable "cantillation" does not get detected in the "IF"
statement
using xlON and always fails the first test. If I use the actual shape name
it
works fine. Anyone have another idea?

Sub ClearBox()
'
' ClearBox Macro
' Macro recorded 1/16/2007 by Marvin
'
Select Case TypeName(Application.Caller)
Case "Range"
Cantillation = Application.Caller.Address
Case "String"
Cantillation = Application.Caller
Case "Error"
Cantillation = "Error"
Case Else
Cantillation = "unknown"
End Select
MsgBox "caller = " & Cantillation
If [Cantillation] = xlOn Then
ActiveSheet.Shapes(Cantillation).Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 13
Else
ActiveSheet.Shapes(Cantillation).Select
Selection.ShapeRange.Fill.Visible = msoFalse
End If
Range("A1").Select
End Sub