Simple hiding columns with vba does not work with a checkbox

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

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
 
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
 
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,

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/
 
Back
Top