Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check Box Macro to hide and unhide a column
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check Box Macro to hide and unhide a column
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 wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check Box Macro to hide and unhide a column
Thank you so much, Dave.
It was of great help!!! =) Dave Peterson wrote: 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 wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check Box Macro to hide and unhide a column
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: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 wrote: Thank you so much, Dave. It was of great help!!! =) Dave Peterson wrote: 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 wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check Box Macro to hide and unhide a column
*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.. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check Box Macro to hide and unhide a column
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: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 wrote: *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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check Box Macro to hide and unhide a column
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?? Dave Peterson wrote: 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: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 wrote: *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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check Box Macro to hide and unhide a column
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?? Daphne wrote: 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?? Dave Peterson wrote: 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: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 wrote: *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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check Box Macro to hide and unhide a column
I don't see anything in the code that would cause it to fail in any version.
Daphne wrote: 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?? Daphne wrote: 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?? Dave Peterson wrote: 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: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 wrote: *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 -- Dave Peterson |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check Box Macro to hide and unhide a column
I will go try it out when i'm back in the office. Thanks so much for
your help... =) Dave Peterson wrote: I don't see anything in the code that would cause it to fail in any version. Daphne wrote: 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?? Daphne wrote: 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?? Dave Peterson wrote: 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: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 wrote: *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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to hide and unhide based on criteria | Excel Discussion (Misc queries) | |||
Excel 97 hide unhide problem | Excel Discussion (Misc queries) | |||
Hide Unhide Hide again | New Users to Excel | |||
How do I hide or unhide zero value columns/rows USING A MACRO? | Excel Discussion (Misc queries) | |||
How Do I Hide A Row (if a condition is true) using a Macro ? | Excel Worksheet Functions |