Hide / Unhide Columns with checkbox

[Sige]

Hi There,

1. I have a checkbox placed on a sheet -via the Control Toolbox- and
following code for the sheet:

Private Sub CheckBox1_Click()
If CheckBox1 = True Then
Run "GSVHide"
Else
Run "GSVUnHide"
End If
End Sub

2. I have 2 subs under a standard module:

Sub GSVHide()
ActiveSheet.Range("B:B").EntireColumn.Hidden = True
End Sub
Sub GSVUnHide()
ActiveSheet.Range("B:B").EntireColumn.Hidden = False
End Sub

=> It Bugs:
Run-time Error 1004:
Unable to set Teh Hidden Propertyof The RAnge Class

- "No" my checkbox is not in a column/ row that is supposed to be
hidden!
- "No" my sheets nor workbook are protected
*? Working in Excel 97 Sr2
*? Is there a problem with my references perhaps?


Any suggestions please Sige

 

[Nigel]

Hi Not much help but your code works fine in xL2002 under XP
I have xL97 under NT in my office I will try it later.

 

[Bob Phillips]

Sige,

The problem is that the checkbox still has focus. Try


Sub GSVHide()
With ActiveSheet
.Range("A1").Activate
.Range("B:B").EntireColumn.Hidden = True
End With
End Sub
Sub GSVUnHide()
With ActiveSheet
.Range("A1").Activate
.Range("B:B").EntireColumn.Hidden = False
End With
End Sub

 

[SIGE]

Hi Bob,
Thx It works nicely!
-I do not completely understand what the focus on the checkbox has to do
with running the Subs...?
-It aint possible to "un-set" the focus of a control in a different way
than activating another range?

Cheers Sige )

"NOSPAM" to be removed for direct mailing...

 

[Bob Phillips]

Sige,

It is a bug, so logic doesn't come into it .

You could activate the activecell (honest) so as not to select another
range, like so

Sub GSVHide()
With ActiveSheet
ActiveCell.Activate
.Range("B:B").EntireColumn.Hidden = True
End With
End Sub
Sub GSVUnHide()
With ActiveSheet
ActiveCell.Activate
.Range("B:B").EntireColumn.Hidden = False
End With
End Sub