Check Box Macro to hide and unhide a column

[Daphne]

Hi all,

I need help to perform this action:

1. When User check the box, it will hide column B(for instance) in
another sheet (for instance, output sheet)
2. When User uncheck the box, it will unhide the column B that User had
hidden when he check the box in step one.

Thanks a great deal!!!!

Cheers
Daphne

 

[Dave Peterson]

I put a a checkbox from the Forms toolbar on a worksheet and assigned it this
macro:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCBX As CheckBox

Set myRng = Worksheets("output").Range("B:B")
Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)

myRng.EntireColumn.Hidden = CBool(myCBX.Value = xlOn)

End Sub



If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Daphne]

Thank you so much, Dave.
It was of great help!!! =)

 

[Daphne]

Hi I have a problem with the code...

It gives me an error msg:
Run-time error('1004')
Unable to get the checkboxes property of the Worksheet Class

My code is as follows:

Option Explicit
Sub HideColumn_Expiry()


Dim myRng As Range
Dim myCbx As CheckBox

Set myRng = Worksheets("Final").Range("C:C")
Set myCbx = ActiveSheet.CheckBoxes(Application.Caller)

myRng.EntireColumn.Hidden = CBool(myCbx.Value = xlOn)


End Sub

However, even though the error msg is there, but when I insert the code
to unprotect and protect a worksheet, the error msg appears.
The code is as follows:

Sub HideColumn_Area()

Sheets("Final").Select
ActiveSheet.Unprotect

Dim myRng As Range
Dim myCbx As CheckBox

Set myRng = Worksheets("Final").Range("D")
Set myCbx = ActiveSheet.CheckBoxes(Application.Caller)

myRng.EntireColumn.Hidden = CBool(myCbx.Value = xlOn)

Sheets("Final").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

End Sub


Please advise.... =(

 

[Daphne]

*Sorry... I mean when i apply the macros before i add in the code for
unprotecting and protecting the code, it still works, but when i add in
the code, the error msg stops me from advancing.

Please advise..

 

[Dave Peterson]

Just a guess...

Are you unprotecting (and reprotecting) the correct worksheet?

Depending on where you placed the code (General module or behind a worksheet),
you may have trouble using Activesheet.

Sub HideColumn_Area()

Sheets("Final").Unprotect

Dim myRng As Range
Dim myCbx As CheckBox

Set myRng = Worksheets("Final").Range("D")
Set myCbx = ActiveSheet.CheckBoxes(Application.Caller)

myRng.EntireColumn.Hidden = CBool(myCbx.Value = xlOn)

Sheets("Final").Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True

End Sub


And remember to pass the password if you used one.

 

[Daphne]

It should be the correct worksheet as it works in other macros,

I place it in the general module, will it work in this case then??

 

[Daphne]

I just tried the Macro at home, and it works. However, it does not work
in my office's excel. Will the edition of the Excel be a factor for not
working??

 

[Dave Peterson]

I don't see anything in the code that would cause it to fail in any version.

 

[Daphne]

I will go try it out when i'm back in the office. Thanks so much for
your help... =)