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

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" <KevinMcCartney@discussions.microsoft.com> wrote in
message news:2DA18C00-F6B5-4F1E-9DBB-5796D22CFC13@microsoft.com...
> 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

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

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

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" <KevinMcCartney@discussions.microsoft.com> wrote in
message news:B44591A2-43AC-4BC6-9772-D706F27A6115@microsoft.com...
> 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