Form Check Boxes an a worksheet

[Guest]

Hi TWIMC,

can someone please tell me why the following line of code doesn't return the
most logical value when a check box is unchecked,

ActiveSheet.CheckBoxes("Check Box 1").Value

Instead of retunring the expected value 0 it returns -4146. If I set a
Linked cell then I do get FALSE and TRUE appearing but I want to reference
the value of the Check Box not the cell.

I want to reference the value of the check box for the following line of code.

ActiveSheet.Columns("D:H").Hidden = ActiveSheet.CheckBoxes("Check Box
1").Value, it works to hide but does't to unhide.

Thanks in advance
KM

 

[Norman Jones]

Hi Kevin,

The unchecked checkbox returns the xlOff constant which is -4146.

Try:

'=============>>
Public Sub Tester011()
MsgBox ActiveSheet. _
CheckBoxes("Check Box 1").Value = xlOff
Debug.Print "xlOff", xlOff
End Sub
'<<=============


In the immediate window you will see the returned xlOff constant value.

 

[Guest]

Kevin :

try,

If ActiveSheet.CheckBoxes("Check Box 1").Value = xlOn Then
ActiveSheet.Columns("D:H").Hidden = True
End If

 

[Guest]

Cheers Chijanzen

The -4146 obviously come from the xl constant xlOff.

Thanks again
regards
KM

 

[Tom Ogilvy]

Do it this way:

ActiveSheet.Columns("D:H").Hidden = (ActiveSheet.CheckBoxes( _
"Check Box 1").Value and 1)

if checked, then they will be hidden; unchecked, unhidden

? xlon and 1
1
? xloff and 1
0

to reverse the sense

ActiveSheet.Columns("D:H").Hidden = not cbool (ActiveSheet.CheckBoxes( _
"Check Box 1").Value and 1)

? not cbool(xlOn and 1)
False
? not cbool(xloff and 1)
True