Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple hiding columns with vba does not work with a checkbox
Hello,
I am a little confused by something that should be very simple. I am trying to have certain columns hide and unhide depending on if a check box is check or not checked. I get an error that says: "Run-time error 1004" "Unable to set the Hidden Property of the Range class" What confuses me most is the following works when placed in a subroutine but not when placed in a checkbox's change function. So this works: Sub test() Columns("A:B").Hidden = Not Columns("A:B").Hidden End Sub And this doesn't: Private Sub CheckBox1_Click() Columns("A:B").Hidden = Not Columns("A:B").Hidden End Sub This is all on a blank new workbook with nothing else to get in the way and I am using Excel 97. The worksheet is not protected and I get the same results when there is data in the cells and when there is no date in the cells. I have also tried included more specific references with workbooks(). and worksheets(). but that doesn't help either. Any help would be much appreciated as I am about to head butt my computer. Thanks, Brian |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple hiding columns with vba does not work with a checkbox
Brian,
Is the code in the sheet module, not just in a general module? If not, that's the problem. In design mode right click the checkbox and select "View Code." Paste your code there. hth, Doug "Brian" wrote in message ... Hello, I am a little confused by something that should be very simple. I am trying to have certain columns hide and unhide depending on if a check box is check or not checked. I get an error that says: "Run-time error 1004" "Unable to set the Hidden Property of the Range class" What confuses me most is the following works when placed in a subroutine but not when placed in a checkbox's change function. So this works: Sub test() Columns("A:B").Hidden = Not Columns("A:B").Hidden End Sub And this doesn't: Private Sub CheckBox1_Click() Columns("A:B").Hidden = Not Columns("A:B").Hidden End Sub This is all on a blank new workbook with nothing else to get in the way and I am using Excel 97. The worksheet is not protected and I get the same results when there is data in the cells and when there is no date in the cells. I have also tried included more specific references with workbooks(). and worksheets(). but that doesn't help either. Any help would be much appreciated as I am about to head butt my computer. Thanks, Brian |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple hiding columns with vba does not work with a checkbox
Hi Brian,
If the check box is on the worksheet, right click it and make sure "move and resize with cells" is checked. Shapes on worksheets (including comments) can interfere with hiding columns. regards Paul "Brian" wrote in message ... Hello, I am a little confused by something that should be very simple. I am trying to have certain columns hide and unhide depending on if a check box is check or not checked. I get an error that says: "Run-time error 1004" "Unable to set the Hidden Property of the Range class" What confuses me most is the following works when placed in a subroutine but not when placed in a checkbox's change function. So this works: Sub test() Columns("A:B").Hidden = Not Columns("A:B").Hidden End Sub And this doesn't: Private Sub CheckBox1_Click() Columns("A:B").Hidden = Not Columns("A:B").Hidden End Sub This is all on a blank new workbook with nothing else to get in the way and I am using Excel 97. The worksheet is not protected and I get the same results when there is data in the cells and when there is no date in the cells. I have also tried included more specific references with workbooks(). and worksheets(). but that doesn't help either. Any help would be much appreciated as I am about to head butt my computer. Thanks, Brian |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple hiding columns with vba does not work with a checkbox
Brian,
Don't head butt your computer, it won't help! (Been there, done that. [g]) Instead, try taking the focus off the ActiveX control before you attempt to do anything else. Under Excel 97, but not 2000 and above, I've found that many simple actions will result in error messages similar to yours if the focus is not explicitly put back on the worksheet first. So your event proc would be something like: Private Sub CheckBox1_Click() Range("A1").Activate Columns("A:B").Hidden = Not Columns("A:B").Hidden End Sub Or in a more general case: Private Sub CheckBox1_Click() 'Put focus back where it was on the sheet if possible. If TypeName(Selection) = "Range" Then Selection.Activate Else ActiveSheet.Range("A1").Activate End If 'Now continue with whatever needs to be done. Columns("A:B").Hidden = Not Columns("A:B").Hidden End Sub HTH, Dan www.qsl.net/ac6la/ "Brian" wrote Hello, I am a little confused by something that should be very simple. I am trying to have certain columns hide and unhide depending on if a check box is check or not checked. I get an error that says: "Run-time error 1004" "Unable to set the Hidden Property of the Range class" What confuses me most is the following works when placed in a subroutine but not when placed in a checkbox's change function. So this works: Sub test() Columns("A:B").Hidden = Not Columns("A:B").Hidden End Sub And this doesn't: Private Sub CheckBox1_Click() Columns("A:B").Hidden = Not Columns("A:B").Hidden End Sub This is all on a blank new workbook with nothing else to get in the way and I am using Excel 97. The worksheet is not protected and I get the same results when there is data in the cells and when there is no date in the cells. I have also tried included more specific references with workbooks(). and worksheets(). but that doesn't help either. Any help would be much appreciated as I am about to head butt my computer. Thanks, Brian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I insert simple checkbox which can be checked by a click? | Excel Discussion (Misc queries) | |||
Inserting a simple checkbox that can be checked | Excel Discussion (Misc queries) | |||
Hiding a ComboBox with a Checkbox | Excel Worksheet Functions | |||
Simple Checkbox Message Code | Excel Discussion (Misc queries) | |||
I need to create a simple checkbox that has true false outputs | Excel Discussion (Misc queries) |