| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VBA for form check boxes | Excel Worksheet Functions | |||
| copy check boxes (made from form toolbar) | Excel Discussion (Misc queries) | |||
| VBA code for the Check Boxes in a form | Excel Discussion (Misc queries) | |||
| How do I create a Form with check boxes in Excel | Excel Worksheet Functions | |||
| Can I Group Form Check Boxes? | Excel Programming | |||
Hybrid Mode
