Form Check Boxes an a worksheet
 

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

Form Check Boxes an a worksheet
 

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.

---
Regards,
Norman


"Kevin McCartney" wrote in
message ...
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

Form Check Boxes an a worksheet
 

Kevin :

try,

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

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"Kevin McCartney" wrote:

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

Form Check Boxes an a worksheet
 

Cheers Chijanzen

The -4146 obviously come from the xl constant xlOff.

Thanks again
regards
KM

"chijanzen" wrote:

Kevin :

try,

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

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"Kevin McCartney" wrote:

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

Form Check Boxes an a worksheet
 

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

--
Regards,
Tom Ogilvy


"Kevin McCartney" wrote in
message ...
Cheers Chijanzen

The -4146 obviously come from the xl constant xlOff.

Thanks again
regards
KM

"chijanzen" wrote:

Kevin :

try,

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

--
???,???????
???,???????

http://www.vba.com.tw/plog/


"Kevin McCartney" wrote:

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