|
Delete/Collapse Rows Not Selected
I have created a spreadsheet with check boxes. If the box is not checked, then I want to delete/collapse the rows. How do I create a macro to do this??
Thanks. |
Delete/Collapse Rows Not Selected
What kind of checkboxes - control toolbox toolbar or forms toolbar
-- Regards, Tom Ogilvy Cindy wrote in message ... I have created a spreadsheet with check boxes. If the box is not checked, then I want to delete/collapse the rows. How do I create a macro to do this?? Thanks. |
Delete/Collapse Rows Not Selected
Forms toolbar.
Thanks for the reply. Cindy Johnson ----- Tom Ogilvy wrote: ----- What kind of checkboxes - control toolbox toolbar or forms toolbar -- Regards, Tom Ogilvy Cindy wrote in message ... I have created a spreadsheet with check boxes. If the box is not checked, then I want to delete/collapse the rows. How do I create a macro to do this?? Thanks. |
Delete/Collapse Rows Not Selected
Thanks Dave! I'll attempt to write this Cindy Johnso ----- Dave Peterson wrote: ---- I think I'd use two macros The first macro would hide the row (and associated checkbox) and the secon would unhide both the rows and the checkboxes Option Explici Sub HideRow( Dim myCBX As CheckBo For Each myCBX In ActiveSheet.CheckBoxe With myCB .Visible = CBool(.Value = xlOn .TopLeftCell.EntireRow.Hidden = CBool(.Value = xlOff End Wit Next myCB End Su Sub ShowCBX( With ActiveShee .Rows.Hidden = Fals .CheckBoxes.Visible = Tru End With End Su Cindy wrote Forms toolbar Thanks for the reply Cindy Johnso ----- Tom Ogilvy wrote: ---- What kind of checkboxes - control toolbox toolbar or forms toolba - Regards Tom Ogilv Cindy wrote in messag .. I have created a spreadsheet with check boxes. If the box is not checked then I want to delete/collapse the rows. How do I create a macro to d this? Thanks -- Dave Peterso |
Delete/Collapse Rows Not Selected
Dave I am attempting to create this macro as you suggested, but I am having a problem see below Sub HideRow( Dim myCBX As CheckBo For Each myCBX In ActiveSheet.CheckBoxe With myCBX When I get to this point and hit enter, I get an error message that says "Compile Error: Action will reset your project, proceed anyway For give my ignorance, but I am no programmer Please help Cind ----- Cindy Johnson wrote: ---- Thanks Dave! I'll attempt to write this Cindy Johnso ----- Dave Peterson wrote: ---- I think I'd use two macros The first macro would hide the row (and associated checkbox) and the secon would unhide both the rows and the checkboxes Option Explici Sub HideRow( Dim myCBX As CheckBo For Each myCBX In ActiveSheet.CheckBoxe With myCB .Visible = CBool(.Value = xlOn .TopLeftCell.EntireRow.Hidden = CBool(.Value = xlOff End Wit Next myCB End Su Sub ShowCBX( With ActiveShee .Rows.Hidden = Fals .CheckBoxes.Visible = Tru End With End Su Cindy wrote Forms toolbar Thanks for the reply Cindy Johnso ----- Tom Ogilvy wrote: ---- What kind of checkboxes - control toolbox toolbar or forms toolba - Regards Tom Ogilv Cindy wrote in messag .. I have created a spreadsheet with check boxes. If the box is not checked then I want to delete/collapse the rows. How do I create a macro to d this? Thanks -- Dave Peterso |
Delete/Collapse Rows Not Selected
If you're in the middle of stepping through your code and you make a change to a
"with" line, then excel figures that it's gonna get confused and throws up its hands (well, if it had hands). Then it gives you an option to say, "I want you to do this and stop the currently running macro" or "oh, oh. That was a mistake. I want a mulligan." So just let the macro be reset and continue. ps. I find it much easier to just copy from the newsgroup posting and paste into the VBE window. (lots less typing.) Cindy Johnson wrote: Dave, I am attempting to create this macro as you suggested, but I am having a problem see below. Sub HideRow() Dim myCBX As CheckBox For Each myCBX In ActiveSheet.CheckBoxes With myCBX When I get to this point and hit enter, I get an error message that says "Compile Error: Action will reset your project, proceed anyway" For give my ignorance, but I am no programmer. Please help. Cindy ----- Cindy Johnson wrote: ----- Thanks Dave! I'll attempt to write this. Cindy Johnson ----- Dave Peterson wrote: ----- I think I'd use two macros. The first macro would hide the row (and associated checkbox) and the second would unhide both the rows and the checkboxes. Option Explicit Sub HideRow() Dim myCBX As CheckBox For Each myCBX In ActiveSheet.CheckBoxes With myCBX .Visible = CBool(.Value = xlOn) .TopLeftCell.EntireRow.Hidden = CBool(.Value = xlOff) End With Next myCBX End Sub Sub ShowCBX() With ActiveSheet .Rows.Hidden = False .CheckBoxes.Visible = True End With End Sub Cindy wrote: Forms toolbar. Thanks for the reply. Cindy Johnson ----- Tom Ogilvy wrote: ----- What kind of checkboxes - control toolbox toolbar or forms toolbar -- Regards, Tom Ogilvy Cindy wrote in message ... I have created a spreadsheet with check boxes. If the box is not checked, then I want to delete/collapse the rows. How do I create a macro to do this?? Thanks. -- Dave Peterson -- Dave Peterson |
Delete/Collapse Rows Not Selected
Dave
I have created two separate macros as you suggested by pasting in both codes. When I run the second macro to unhide the rows and checkboxes it doesn't work. It highlights the .Rows... and says that there is a complie error: Invalid or unqualified reference Thanks for all your help Cind ----- Dave Peterson wrote: ---- If you're in the middle of stepping through your code and you make a change to "with" line, then excel figures that it's gonna get confused and throws up it hands (well, if it had hands) Then it gives you an option to say, "I want you to do this and stop th currently running macro" or "oh, oh. That was a mistake. I want a mulligan. So just let the macro be reset and continue ps. I find it much easier to just copy from the newsgroup posting and past into the VBE window. (lots less typing. Cindy Johnson wrote Dave I am attempting to create this macro as you suggested, but I am having a problem see below Sub HideRow( Dim myCBX As CheckBo For Each myCBX In ActiveSheet.CheckBoxe With myCB When I get to this point and hit enter, I get an error message that says "Compile Error: Action will reset your project, proceed anyway For give my ignorance, but I am no programmer Please help Cind ----- Cindy Johnson wrote: ---- Thanks Dave! I'll attempt to write this Cindy Johnso ----- Dave Peterson wrote: ---- I think I'd use two macros The first macro would hide the row (and associated checkbox) and the secon would unhide both the rows and the checkboxes Option Explici Sub HideRow( Dim myCBX As CheckBo For Each myCBX In ActiveSheet.CheckBoxe With myCB .Visible = CBool(.Value = xlOn .TopLeftCell.EntireRow.Hidden = CBool(.Value = xlOff End Wit Next myCB End Su Sub ShowCBX( With ActiveShee .Rows.Hidden = Fals .CheckBoxes.Visible = Tru End Wit End Su Cindy wrote Forms toolbar Thanks for the reply Cindy Johnso ----- Tom Ogilvy wrote: ---- What kind of checkboxes - control toolbox toolbar or forms toolba - Regards Tom Ogilv Cindy wrote in messag .. I have created a spreadsheet with check boxes. If the box is not checked then I want to delete/collapse the rows. How do I create a macro to d this? Thanks - Dave Peterso -- Dave Peterso |
Delete/Collapse Rows Not Selected
Did you include that:
with Activesheet the leading dots means that it's associated with a "with" statement--the one directly preceding that line. Cindy Johnson wrote: Dave, I have created two separate macros as you suggested by pasting in both codes. When I run the second macro to unhide the rows and checkboxes it doesn't work. It highlights the .Rows... and says that there is a complie error: Invalid or unqualified reference. Thanks for all your help. Cindy ----- Dave Peterson wrote: ----- If you're in the middle of stepping through your code and you make a change to a "with" line, then excel figures that it's gonna get confused and throws up its hands (well, if it had hands). Then it gives you an option to say, "I want you to do this and stop the currently running macro" or "oh, oh. That was a mistake. I want a mulligan." So just let the macro be reset and continue. ps. I find it much easier to just copy from the newsgroup posting and paste into the VBE window. (lots less typing.) Cindy Johnson wrote: Dave, I am attempting to create this macro as you suggested, but I am having a problem see below. Sub HideRow() Dim myCBX As CheckBox For Each myCBX In ActiveSheet.CheckBoxes With myCBX When I get to this point and hit enter, I get an error message that says "Compile Error: Action will reset your project, proceed anyway" For give my ignorance, but I am no programmer. Please help. Cindy ----- Cindy Johnson wrote: ----- Thanks Dave! I'll attempt to write this. Cindy Johnson ----- Dave Peterson wrote: ----- I think I'd use two macros. The first macro would hide the row (and associated checkbox) and the second would unhide both the rows and the checkboxes. Option Explicit Sub HideRow() Dim myCBX As CheckBox For Each myCBX In ActiveSheet.CheckBoxes With myCBX .Visible = CBool(.Value = xlOn) .TopLeftCell.EntireRow.Hidden = CBool(.Value = xlOff) End With Next myCBX End Sub Sub ShowCBX() With ActiveSheet .Rows.Hidden = False .CheckBoxes.Visible = True End With End Sub Cindy wrote: Forms toolbar. Thanks for the reply. Cindy Johnson ----- Tom Ogilvy wrote: ----- What kind of checkboxes - control toolbox toolbar or forms toolbar -- Regards, Tom Ogilvy Cindy wrote in message ... I have created a spreadsheet with check boxes. If the box is not checked, then I want to delete/collapse the rows. How do I create a macro to do this?? Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Delete/Collapse Rows Not Selected
Yes, I copied and pasted it in. So, it is exactly as you typed.
----- Dave Peterson wrote: ---- Did you include that with Activeshee the leading dots means that it's associated with a "with" statement--the on directly preceding that line Cindy Johnson wrote Dave I have created two separate macros as you suggested by pasting in both codes When I run the second macro to unhide the rows and checkboxes it doesn't work It highlights the .Rows... and says that there is a complie error: Invalid or unqualified reference Thanks for all your help Cind ----- Dave Peterson wrote: ---- If you're in the middle of stepping through your code and you make a change to "with" line, then excel figures that it's gonna get confused and throws up it hands (well, if it had hands) Then it gives you an option to say, "I want you to do this and stop th currently running macro" or "oh, oh. That was a mistake. I want a mulligan. So just let the macro be reset and continue ps. I find it much easier to just copy from the newsgroup posting and past into the VBE window. (lots less typing. Cindy Johnson wrote Dave I am attempting to create this macro as you suggested, but I am having a problem see below Sub HideRow( Dim myCBX As CheckBo For Each myCBX In ActiveSheet.CheckBoxe With myCB When I get to this point and hit enter, I get an error message that says "Compile Error: Action will reset your project, proceed anyway For give my ignorance, but I am no programmer Please help Cind ----- Cindy Johnson wrote: ---- Thanks Dave! I'll attempt to write this Cindy Johnso ----- Dave Peterson wrote: ---- I think I'd use two macros The first macro would hide the row (and associated checkbox) and the secon would unhide both the rows and the checkboxes Option Explici Sub HideRow( Dim myCBX As CheckBo For Each myCBX In ActiveSheet.CheckBoxe With myCB .Visible = CBool(.Value = xlOn .TopLeftCell.EntireRow.Hidden = CBool(.Value = xlOff End Wit Next myCB End Su Sub ShowCBX( With ActiveShee .Rows.Hidden = Fals .CheckBoxes.Visible = Tru End Wit End Su Cindy wrote Forms toolbar Thanks for the reply Cindy Johnso ----- Tom Ogilvy wrote: ---- What kind of checkboxes - control toolbox toolbar or forms toolba - Regards Tom Ogilv Cindy wrote in messag .. I have created a spreadsheet with check boxes. If the box is not checked then I want to delete/collapse the rows. How do I create a macro to d this? Thanks - Dave Peterso - Dave Peterso -- Dave Peterso |
Delete/Collapse Rows Not Selected
It worked ok for me.
I guess the next questions a What version of excel are you using? where is the code located--a general module or behind a worksheet/thisworkbook? How are you executing the code? I don't ever remember seeing a compile error with this bug, but if you're using xl97 and are running the code from a commandbutton from the control toolbox (directly on the worksheet), then try changing the .takefocusonclick property to false. If the control doesn't have a .takefocusonclick property, then add: activecell.activate to the top of the code. But this bug usually gives a 1004 error--not a compile error. Maybe it's a missing reference. Inside the VBE with this project active, click on tools|references. Scroll down that list looking for a Missing Reference. (If you have a missing reference, it's not always related to the line that stops the code.) And if you do find it, you'll either have to remove that reference or if you think you need it, you'll have to find it. Other than that, I don't have a better guess. Cindy wrote: Yes, I copied and pasted it in. So, it is exactly as you typed. ----- Dave Peterson wrote: ----- Did you include that: with Activesheet the leading dots means that it's associated with a "with" statement--the one directly preceding that line. Cindy Johnson wrote: Dave, I have created two separate macros as you suggested by pasting in both codes. When I run the second macro to unhide the rows and checkboxes it doesn't work. It highlights the .Rows... and says that there is a complie error: Invalid or unqualified reference. Thanks for all your help. Cindy ----- Dave Peterson wrote: ----- If you're in the middle of stepping through your code and you make a change to a "with" line, then excel figures that it's gonna get confused and throws up its hands (well, if it had hands). Then it gives you an option to say, "I want you to do this and stop the currently running macro" or "oh, oh. That was a mistake. I want a mulligan." So just let the macro be reset and continue. ps. I find it much easier to just copy from the newsgroup posting and paste into the VBE window. (lots less typing.) Cindy Johnson wrote: Dave, I am attempting to create this macro as you suggested, but I am having a problem see below. Sub HideRow() Dim myCBX As CheckBox For Each myCBX In ActiveSheet.CheckBoxes With myCBX When I get to this point and hit enter, I get an error message that says "Compile Error: Action will reset your project, proceed anyway" For give my ignorance, but I am no programmer. Please help. Cindy ----- Cindy Johnson wrote: ----- Thanks Dave! I'll attempt to write this. Cindy Johnson ----- Dave Peterson wrote: ----- I think I'd use two macros. The first macro would hide the row (and associated checkbox) and the second would unhide both the rows and the checkboxes. Option Explicit Sub HideRow() Dim myCBX As CheckBox For Each myCBX In ActiveSheet.CheckBoxes With myCBX .Visible = CBool(.Value = xlOn) .TopLeftCell.EntireRow.Hidden = CBool(.Value = xlOff) End With Next myCBX End Sub Sub ShowCBX() With ActiveSheet .Rows.Hidden = False .CheckBoxes.Visible = True End With End Sub Cindy wrote: Forms toolbar. Thanks for the reply. Cindy Johnson ----- Tom Ogilvy wrote: ----- What kind of checkboxes - control toolbox toolbar or forms toolbar -- Regards, Tom Ogilvy Cindy wrote in message ... I have created a spreadsheet with check boxes. If the box is not checked, then I want to delete/collapse the rows. How do I create a macro to do this?? Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Delete/Collapse Rows Not Selected
Does the code look exactly like this:
Sub ShowCBX() With ActiveSheet .Rows.Hidden = False .CheckBoxes.Visible = True End With End Sub Note that there is only a single period at the beginning of Rows and Checkboxes -- Regards, Tom Ogilvy Cindy wrote in message ... Yes, I copied and pasted it in. So, it is exactly as you typed. ----- Dave Peterson wrote: ----- Did you include that: with Activesheet the leading dots means that it's associated with a "with" statement--the one directly preceding that line. Cindy Johnson wrote: Dave, I have created two separate macros as you suggested by pasting in both codes. When I run the second macro to unhide the rows and checkboxes it doesn't work. It highlights the .Rows... and says that there is a complie error: Invalid or unqualified reference. Thanks for all your help. Cindy ----- Dave Peterson wrote: ----- If you're in the middle of stepping through your code and you make a change to a "with" line, then excel figures that it's gonna get confused and throws up its hands (well, if it had hands). Then it gives you an option to say, "I want you to do this and stop the currently running macro" or "oh, oh. That was a mistake. I want a mulligan." So just let the macro be reset and continue. ps. I find it much easier to just copy from the newsgroup posting and paste into the VBE window. (lots less typing.) Cindy Johnson wrote: Dave, I am attempting to create this macro as you suggested, but I am having a problem see below. Sub HideRow() Dim myCBX As CheckBox For Each myCBX In ActiveSheet.CheckBoxes With myCBX When I get to this point and hit enter, I get an error message that says "Compile Error: Action will reset your project, proceed anyway" For give my ignorance, but I am no programmer. Please help. Cindy ----- Cindy Johnson wrote: ----- Thanks Dave! I'll attempt to write this. Cindy Johnson ----- Dave Peterson wrote: ----- I think I'd use two macros. The first macro would hide the row (and associated checkbox) and the second would unhide both the rows and the checkboxes. Option Explicit Sub HideRow() Dim myCBX As CheckBox For Each myCBX In ActiveSheet.CheckBoxes With myCBX .Visible = CBool(.Value = xlOn) .TopLeftCell.EntireRow.Hidden = CBool(.Value = xlOff) End With Next myCBX End Sub Sub ShowCBX() With ActiveSheet .Rows.Hidden = False .CheckBoxes.Visible = True End With End Sub Cindy wrote: Forms toolbar. Thanks for the reply. Cindy Johnson ----- Tom Ogilvy wrote: ----- What kind of checkboxes - control toolbox toolbar or forms toolbar -- Regards, Tom Ogilvy Cindy wrote in message ... I have created a spreadsheet with check boxes. If the box is not checked, then I want to delete/collapse the rows. How do I create a macro to do this?? Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Delete/Collapse Rows Not Selected
I am using 2002.
The code is located in the worksheet/this workbook. To execute I go to macros, select the one I want and click run. I added activecell.activate to the top of the code for the ShowCBX macro and it unhides the rows but gives a Run Time Error 1004: Unable to set the visible property of the checkboxes class. The first macro works great and that is really what I was looking for. It would be nice if the other worked properly, but is not a necessity. I appreciate the time you have taken to help someone who has no idea of what they are doing when it comes to this. Cindy ----- Dave Peterson wrote: ----- It worked ok for me. I guess the next questions a What version of excel are you using? where is the code located--a general module or behind a worksheet/thisworkbook? How are you executing the code? I don't ever remember seeing a compile error with this bug, but if you're using xl97 and are running the code from a commandbutton from the control toolbox (directly on the worksheet), then try changing the .takefocusonclick property to false. If the control doesn't have a .takefocusonclick property, then add: activecell.activate to the top of the code. But this bug usually gives a 1004 error--not a compile error. Maybe it's a missing reference. Inside the VBE with this project active, click on tools|references. Scroll down that list looking for a Missing Reference. (If you have a missing reference, it's not always related to the line that stops the code.) And if you do find it, you'll either have to remove that reference or if you think you need it, you'll have to find it. Other than that, I don't have a better guess. Cindy wrote: Yes, I copied and pasted it in. So, it is exactly as you typed. ----- Dave Peterson wrote: ----- Did you include that: with Activesheet the leading dots means that it's associated with a "with" statement--the one directly preceding that line. Cindy Johnson wrote: Dave, I have created two separate macros as you suggested by pasting in both codes. When I run the second macro to unhide the rows and checkboxes it doesn't work. It highlights the .Rows... and says that there is a complie error: Invalid or unqualified reference. Thanks for all your help. Cindy ----- Dave Peterson wrote: ----- If you're in the middle of stepping through your code and you make a change to a "with" line, then excel figures that it's gonna get confused and throws up its hands (well, if it had hands). Then it gives you an option to say, "I want you to do this and stop the currently running macro" or "oh, oh. That was a mistake. I want a mulligan." So just let the macro be reset and continue. ps. I find it much easier to just copy from the newsgroup posting and paste into the VBE window. (lots less typing.) Cindy Johnson wrote: Dave, I am attempting to create this macro as you suggested, but I am having a problem see below. Sub HideRow() Dim myCBX As CheckBox For Each myCBX In ActiveSheet.CheckBoxes With myCBX When I get to this point and hit enter, I get an error message that says "Compile Error: Action will reset your project, proceed anyway" For give my ignorance, but I am no programmer. Please help. Cindy ----- Cindy Johnson wrote: ----- Thanks Dave! I'll attempt to write this. Cindy Johnson ----- Dave Peterson wrote: ----- I think I'd use two macros. The first macro would hide the row (and associated checkbox) and the second would unhide both the rows and the checkboxes. Option Explicit Sub HideRow() Dim myCBX As CheckBox For Each myCBX In ActiveSheet.CheckBoxes With myCBX .Visible = CBool(.Value = xlOn) .TopLeftCell.EntireRow.Hidden = CBool(.Value = xlOff) End With Next myCBX End Sub Sub ShowCBX() With ActiveSheet .Rows.Hidden = False .CheckBoxes.Visible = True End With End Sub Cindy wrote: Forms toolbar. Thanks for the reply. Cindy Johnson ----- Tom Ogilvy wrote: ----- What kind of checkboxes - control toolbox toolbar or forms toolbar -- Regards, Tom Ogilvy Cindy wrote in message ... I have created a spreadsheet with check boxes. If the box is not checked, then I want to delete/collapse the rows. How do I create a macro to do this?? Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Delete/Collapse Rows Not Selected
I don't really have a good next guess.
Sub ShowCBX() With ActiveSheet .Rows.Hidden = False .CheckBoxes.Visible = True End With End Sub (I just pasted from the original post.) This code uses the activesheet. Is there a chance that the wrong sheet is active? Maybe that sheet doesn't have any checkboxes on it. This may sound like I don't trust you, but could you paste the code that you're using if this wasn't it? Maybe someone will see something right away. (Yeah, you said that you pasted directly, but ....) Cindy Johnson wrote: I am using 2002. The code is located in the worksheet/this workbook. To execute I go to macros, select the one I want and click run. I added activecell.activate to the top of the code for the ShowCBX macro and it unhides the rows but gives a Run Time Error 1004: Unable to set the visible property of the checkboxes class. The first macro works great and that is really what I was looking for. It would be nice if the other worked properly, but is not a necessity. I appreciate the time you have taken to help someone who has no idea of what they are doing when it comes to this. Cindy ----- Dave Peterson wrote: ----- It worked ok for me. I guess the next questions a What version of excel are you using? where is the code located--a general module or behind a worksheet/thisworkbook? How are you executing the code? I don't ever remember seeing a compile error with this bug, but if you're using xl97 and are running the code from a commandbutton from the control toolbox (directly on the worksheet), then try changing the .takefocusonclick property to false. If the control doesn't have a .takefocusonclick property, then add: activecell.activate to the top of the code. But this bug usually gives a 1004 error--not a compile error. Maybe it's a missing reference. Inside the VBE with this project active, click on tools|references. Scroll down that list looking for a Missing Reference. (If you have a missing reference, it's not always related to the line that stops the code.) And if you do find it, you'll either have to remove that reference or if you think you need it, you'll have to find it. Other than that, I don't have a better guess. Cindy wrote: Yes, I copied and pasted it in. So, it is exactly as you typed. ----- Dave Peterson wrote: ----- Did you include that: with Activesheet the leading dots means that it's associated with a "with" statement--the one directly preceding that line. Cindy Johnson wrote: Dave, I have created two separate macros as you suggested by pasting in both codes. When I run the second macro to unhide the rows and checkboxes it doesn't work. It highlights the .Rows... and says that there is a complie error: Invalid or unqualified reference. Thanks for all your help. Cindy ----- Dave Peterson wrote: ----- If you're in the middle of stepping through your code and you make a change to a "with" line, then excel figures that it's gonna get confused and throws up its hands (well, if it had hands). Then it gives you an option to say, "I want you to do this and stop the currently running macro" or "oh, oh. That was a mistake. I want a mulligan." So just let the macro be reset and continue. ps. I find it much easier to just copy from the newsgroup posting and paste into the VBE window. (lots less typing.) Cindy Johnson wrote: Dave, I am attempting to create this macro as you suggested, but I am having a problem see below. Sub HideRow() Dim myCBX As CheckBox For Each myCBX In ActiveSheet.CheckBoxes With myCBX When I get to this point and hit enter, I get an error message that says "Compile Error: Action will reset your project, proceed anyway" For give my ignorance, but I am no programmer. Please help. Cindy ----- Cindy Johnson wrote: ----- Thanks Dave! I'll attempt to write this. Cindy Johnson ----- Dave Peterson wrote: ----- I think I'd use two macros. The first macro would hide the row (and associated checkbox) and the second would unhide both the rows and the checkboxes. Option Explicit Sub HideRow() Dim myCBX As CheckBox For Each myCBX In ActiveSheet.CheckBoxes With myCBX .Visible = CBool(.Value = xlOn) .TopLeftCell.EntireRow.Hidden = CBool(.Value = xlOff) End With Next myCBX End Sub Sub ShowCBX() With ActiveSheet .Rows.Hidden = False .CheckBoxes.Visible = True End With End Sub Cindy wrote: Forms toolbar. Thanks for the reply. Cindy Johnson ----- Tom Ogilvy wrote: ----- What kind of checkboxes - control toolbox toolbar or forms toolbar -- Regards, Tom Ogilvy Cindy wrote in message ... I have created a spreadsheet with check boxes. If the box is not checked, then I want to delete/collapse the rows. How do I create a macro to do this?? Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Delete/Collapse Rows Not Selected
Sure see below. I created both of these in the same workbook.
This is the first macro: Sub HideRow() ' Dim myCBX As CheckBox For Each myCBX In ActiveSheet.CheckBoxes With myCBX .Visible = CBool(.Value = xlOn) .TopLeftCell.EntireRow.Hidden = CBool(.Value = xlOff) End With Next myCBX ' HideRow Macro ' Macro recorded 11/13/2003 by cajohnson ' ' End Sub This is the second macro: Sub ShowCBX() ' With ActiveSheet .Rows.Hidden = False .CheckBoxes.Visible = True End With ' ShowCBX Macro ' Macro recorded 11/13/2003 by cajohnson ' ' End Sub The second macro written as above doesn't work. When I added the activecell.activate to the top of the code (see below), it would unhide the rows but gives a Run Time Error 1004: Unable to set the visible property of the checkboxes class. Sub ShowCBX() 'activecell.activate With ActiveSheet .Rows.Hidden = False .CheckBoxes.Visible = True End With ' ShowCBX Macro ' Macro recorded 11/13/2003 by cajohnson ' ' End Sub At this point, I'm totally lost. Cindy ----- Dave Peterson wrote: ----- I don't really have a good next guess. Sub ShowCBX() With ActiveSheet .Rows.Hidden = False .CheckBoxes.Visible = True End With End Sub (I just pasted from the original post.) This code uses the activesheet. Is there a chance that the wrong sheet is active? Maybe that sheet doesn't have any checkboxes on it. This may sound like I don't trust you, but could you paste the code that you're using if this wasn't it? Maybe someone will see something right away. (Yeah, you said that you pasted directly, but ....) Cindy Johnson wrote: I am using 2002. The code is located in the worksheet/this workbook. To execute I go to macros, select the one I want and click run. I added activecell.activate to the top of the code for the ShowCBX macro and it unhides the rows but gives a Run Time Error 1004: Unable to set the visible property of the checkboxes class. The first macro works great and that is really what I was looking for. It would be nice if the other worked properly, but is not a necessity. I appreciate the time you have taken to help someone who has no idea of what they are doing when it comes to this. Cindy ----- Dave Peterson wrote: ----- It worked ok for me. I guess the next questions a What version of excel are you using? where is the code located--a general module or behind a worksheet/thisworkbook? How are you executing the code? I don't ever remember seeing a compile error with this bug, but if you're using xl97 and are running the code from a commandbutton from the control toolbox (directly on the worksheet), then try changing the .takefocusonclick property to false. If the control doesn't have a .takefocusonclick property, then add: activecell.activate to the top of the code. But this bug usually gives a 1004 error--not a compile error. Maybe it's a missing reference. Inside the VBE with this project active, click on tools|references. Scroll down that list looking for a Missing Reference. (If you have a missing reference, it's not always related to the line that stops the code.) And if you do find it, you'll either have to remove that reference or if you think you need it, you'll have to find it. Other than that, I don't have a better guess. Cindy wrote: Yes, I copied and pasted it in. So, it is exactly as you typed. ----- Dave Peterson wrote: ----- Did you include that: with Activesheet the leading dots means that it's associated with a "with" statement--the one directly preceding that line. Cindy Johnson wrote: Dave, I have created two separate macros as you suggested by pasting in both codes. When I run the second macro to unhide the rows and checkboxes it doesn't work. It highlights the .Rows... and says that there is a complie error: Invalid or unqualified reference. Thanks for all your help. Cindy ----- Dave Peterson wrote: ----- If you're in the middle of stepping through your code and you make a change to a "with" line, then excel figures that it's gonna get confused and throws up its hands (well, if it had hands). Then it gives you an option to say, "I want you to do this and stop the currently running macro" or "oh, oh. That was a mistake. I want a mulligan." So just let the macro be reset and continue. ps. I find it much easier to just copy from the newsgroup posting and paste into the VBE window. (lots less typing.) Cindy Johnson wrote: Dave, I am attempting to create this macro as you suggested, but I am having a problem see below. Sub HideRow() Dim myCBX As CheckBox For Each myCBX In ActiveSheet.CheckBoxes With myCBX When I get to this point and hit enter, I get an error message that says "Compile Error: Action will reset your project, proceed anyway" For give my ignorance, but I am no programmer. Please help. Cindy ----- Cindy Johnson wrote: ----- Thanks Dave! I'll attempt to write this. Cindy Johnson ----- Dave Peterson wrote: ----- I think I'd use two macros. The first macro would hide the row (and associated checkbox) and the second would unhide both the rows and the checkboxes. Option Explicit Sub HideRow() Dim myCBX As CheckBox For Each myCBX In ActiveSheet.CheckBoxes With myCBX .Visible = CBool(.Value = xlOn) .TopLeftCell.EntireRow.Hidden = CBool(.Value = xlOff) End With Next myCBX End Sub Sub ShowCBX() With ActiveSheet .Rows.Hidden = False .CheckBoxes.Visible = True End With End Sub Cindy wrote: Forms toolbar. Thanks for the reply. Cindy Johnson ----- Tom Ogilvy wrote: ----- What kind of checkboxes - control toolbox toolbar or forms toolbar -- Regards, Tom Ogilvy Cindy wrote in message ... I have created a spreadsheet with check boxes. If the box is not checked, then I want to delete/collapse the rows. How do I create a macro to do this?? Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Delete/Collapse Rows Not Selected
Ooh. You commented out the "with activesheet" with that apostrophe:
' With ActiveSheet And you never got to the next error about having an "end with" without a "with". Remove that apostrophe and try it again. Cindy Johnson wrote: Sure see below. I created both of these in the same workbook. This is the first macro: Sub HideRow() ' Dim myCBX As CheckBox For Each myCBX In ActiveSheet.CheckBoxes With myCBX .Visible = CBool(.Value = xlOn) .TopLeftCell.EntireRow.Hidden = CBool(.Value = xlOff) End With Next myCBX ' HideRow Macro ' Macro recorded 11/13/2003 by cajohnson ' ' End Sub This is the second macro: Sub ShowCBX() ' With ActiveSheet .Rows.Hidden = False .CheckBoxes.Visible = True End With ' ShowCBX Macro ' Macro recorded 11/13/2003 by cajohnson ' ' End Sub The second macro written as above doesn't work. When I added the activecell.activate to the top of the code (see below), it would unhide the rows but gives a Run Time Error 1004: Unable to set the visible property of the checkboxes class. Sub ShowCBX() 'activecell.activate With ActiveSheet .Rows.Hidden = False .CheckBoxes.Visible = True End With ' ShowCBX Macro ' Macro recorded 11/13/2003 by cajohnson ' ' End Sub At this point, I'm totally lost. Cindy ----- Dave Peterson wrote: ----- I don't really have a good next guess. Sub ShowCBX() With ActiveSheet .Rows.Hidden = False .CheckBoxes.Visible = True End With End Sub (I just pasted from the original post.) This code uses the activesheet. Is there a chance that the wrong sheet is active? Maybe that sheet doesn't have any checkboxes on it. This may sound like I don't trust you, but could you paste the code that you're using if this wasn't it? Maybe someone will see something right away. (Yeah, you said that you pasted directly, but ....) Cindy Johnson wrote: I am using 2002. The code is located in the worksheet/this workbook. To execute I go to macros, select the one I want and click run. I added activecell.activate to the top of the code for the ShowCBX macro and it unhides the rows but gives a Run Time Error 1004: Unable to set the visible property of the checkboxes class. The first macro works great and that is really what I was looking for. It would be nice if the other worked properly, but is not a necessity. I appreciate the time you have taken to help someone who has no idea of what they are doing when it comes to this. Cindy ----- Dave Peterson wrote: ----- It worked ok for me. I guess the next questions a What version of excel are you using? where is the code located--a general module or behind a worksheet/thisworkbook? How are you executing the code? I don't ever remember seeing a compile error with this bug, but if you're using xl97 and are running the code from a commandbutton from the control toolbox (directly on the worksheet), then try changing the .takefocusonclick property to false. If the control doesn't have a .takefocusonclick property, then add: activecell.activate to the top of the code. But this bug usually gives a 1004 error--not a compile error. Maybe it's a missing reference. Inside the VBE with this project active, click on tools|references. Scroll down that list looking for a Missing Reference. (If you have a missing reference, it's not always related to the line that stops the code.) And if you do find it, you'll either have to remove that reference or if you think you need it, you'll have to find it. Other than that, I don't have a better guess. Cindy wrote: Yes, I copied and pasted it in. So, it is exactly as you typed. ----- Dave Peterson wrote: ----- Did you include that: with Activesheet the leading dots means that it's associated with a "with" statement--the one directly preceding that line. Cindy Johnson wrote: Dave, I have created two separate macros as you suggested by pasting in both codes. When I run the second macro to unhide the rows and checkboxes it doesn't work. It highlights the .Rows... and says that there is a complie error: Invalid or unqualified reference. Thanks for all your help. Cindy ----- Dave Peterson wrote: ----- If you're in the middle of stepping through your code and you make a change to a "with" line, then excel figures that it's gonna get confused and throws up its hands (well, if it had hands). Then it gives you an option to say, "I want you to do this and stop the currently running macro" or "oh, oh. That was a mistake. I want a mulligan." So just let the macro be reset and continue. ps. I find it much easier to just copy from the newsgroup posting and paste into the VBE window. (lots less typing.) Cindy Johnson wrote: Dave, I am attempting to create this macro as you suggested, but I am having a problem see below. Sub HideRow() Dim myCBX As CheckBox For Each myCBX In ActiveSheet.CheckBoxes With myCBX When I get to this point and hit enter, I get an error message that says "Compile Error: Action will reset your project, proceed anyway" For give my ignorance, but I am no programmer. Please help. Cindy ----- Cindy Johnson wrote: ----- Thanks Dave! I'll attempt to write this. Cindy Johnson ----- Dave Peterson wrote: ----- I think I'd use two macros. The first macro would hide the row (and associated checkbox) and the second would unhide both the rows and the checkboxes. Option Explicit Sub HideRow() Dim myCBX As CheckBox For Each myCBX In ActiveSheet.CheckBoxes With myCBX .Visible = CBool(.Value = xlOn) .TopLeftCell.EntireRow.Hidden = CBool(.Value = xlOff) End With Next myCBX End Sub Sub ShowCBX() With ActiveSheet .Rows.Hidden = False .CheckBoxes.Visible = True End With End Sub Cindy wrote: Forms toolbar. Thanks for the reply. Cindy Johnson ----- Tom Ogilvy wrote: ----- What kind of checkboxes - control toolbox toolbar or forms toolbar -- Regards, Tom Ogilvy Cindy wrote in message ... I have created a spreadsheet with check boxes. If the box is not checked, then I want to delete/collapse the rows. How do I create a macro to do this?? Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Delete/Collapse Rows Not Selected
Acutally, it was there when I stepped into the macro, I didn't know it couldn't be in front of it. I told you didn't know what I was doing
Anyways, taking the apostrophe out worked, but it still gives the run time error Run Time Error 1004: Unable to set the visible property of the checkboxes class Thanks for your patience and help it is greatly appreciated : Cindy ----- Dave Peterson wrote: ---- Ooh. You commented out the "with activesheet" with that apostrophe ' With ActiveShee And you never got to the next error about having an "end with" without a "with" Remove that apostrophe and try it again |
Delete/Collapse Rows Not Selected
The macro runs against the activesheet. Are you sure that the sheet that is
currently active has checkboxes? Option Explicit Sub ShowCBX() With ActiveSheet .Rows.Hidden = False If .CheckBoxes.Count 0 Then .CheckBoxes.Visible = True Else MsgBox "I think you're on the wrong worksheet" End If End With End Sub If that's not it, what version of excel are you using and how are you excuting the macro--a button from the control toolbox toolbar placed on the worksheet? (If you're using xl97 and yes to the control toolbox stuff, then try adding this line to the top of the code: activecell.activate like in this: Option Explicit Sub ShowCBX() With ActiveSheet activecell.activate .Rows.Hidden = False If .CheckBoxes.Count 0 Then .CheckBoxes.Visible = True Else MsgBox "I think you're on the wrong worksheet" End If End With End Sub Cindy Johnson wrote: Acutally, it was there when I stepped into the macro, I didn't know it couldn't be in front of it. I told you didn't know what I was doing. Anyways, taking the apostrophe out worked, but it still gives the run time error: Run Time Error 1004: Unable to set the visible property of the checkboxes class. Thanks for your patience and help it is greatly appreciated :) Cindy ----- Dave Peterson wrote: ----- Ooh. You commented out the "with activesheet" with that apostrophe: ' With ActiveSheet And you never got to the next error about having an "end with" without a "with". Remove that apostrophe and try it again. -- Dave Peterson |
Delete/Collapse Rows Not Selected
Yes the activesheet has checkboxes or should I say had checkboxes until I ran the 1st macro. Nothing seems to bring back the checkbox. At this point you have helped me to accomplish my 1st goal of running a macro to delete the rows that were not selected, so I'm happy with that. Once a sheet has been set for a specific project there should be no reason to bring back any of the rows deleted.
Cindy ----- Dave Peterson wrote: ---- The macro runs against the activesheet. Are you sure that the sheet that i currently active has checkboxes Option Explici Sub ShowCBX( With ActiveShee .Rows.Hidden = Fals If .CheckBoxes.Count 0 The .CheckBoxes.Visible = Tru Els MsgBox "I think you're on the wrong worksheet End I End Wit End Su If that's not it, what version of excel are you using and how are you excutin the macro--a button from the control toolbox toolbar placed on the worksheet (If you're using xl97 and yes to the control toolbox stuff, then try adding thi line to the top of the code activecell.activat like in this Option Explici Sub ShowCBX( With ActiveShee activecell.activat .Rows.Hidden = Fals If .CheckBoxes.Count 0 The .CheckBoxes.Visible = Tru Els MsgBox "I think you're on the wrong worksheet End I End Wit End Su Cindy Johnson wrote Acutally, it was there when I stepped into the macro, I didn't know it couldn't be in front of it. I told you didn't know what I was doing Anyways, taking the apostrophe out worked, but it still gives the run time error Run Time Error 1004: Unable to set the visible property of the checkboxes class Thanks for your patience and help it is greatly appreciated : Cind ----- Dave Peterson wrote: ---- Ooh. You commented out the "with activesheet" with that apostrophe ' With ActiveShee And you never got to the next error about having an "end with" without a "with" Remove that apostrophe and try it again -- Dave Peterso |
Delete/Collapse Rows Not Selected
Well, glad you got part of it working.
Cindy Johnson wrote: Yes the activesheet has checkboxes or should I say had checkboxes until I ran the 1st macro. Nothing seems to bring back the checkbox. At this point you have helped me to accomplish my 1st goal of running a macro to delete the rows that were not selected, so I'm happy with that. Once a sheet has been set for a specific project there should be no reason to bring back any of the rows deleted. Cindy ----- Dave Peterson wrote: ----- The macro runs against the activesheet. Are you sure that the sheet that is currently active has checkboxes? Option Explicit Sub ShowCBX() With ActiveSheet .Rows.Hidden = False If .CheckBoxes.Count 0 Then .CheckBoxes.Visible = True Else MsgBox "I think you're on the wrong worksheet" End If End With End Sub If that's not it, what version of excel are you using and how are you excuting the macro--a button from the control toolbox toolbar placed on the worksheet? (If you're using xl97 and yes to the control toolbox stuff, then try adding this line to the top of the code: activecell.activate like in this: Option Explicit Sub ShowCBX() With ActiveSheet activecell.activate .Rows.Hidden = False If .CheckBoxes.Count 0 Then .CheckBoxes.Visible = True Else MsgBox "I think you're on the wrong worksheet" End If End With End Sub Cindy Johnson wrote: Acutally, it was there when I stepped into the macro, I didn't know it couldn't be in front of it. I told you didn't know what I was doing. Anyways, taking the apostrophe out worked, but it still gives the run time error: Run Time Error 1004: Unable to set the visible property of the checkboxes class. Thanks for your patience and help it is greatly appreciated :) Cindy ----- Dave Peterson wrote: ----- Ooh. You commented out the "with activesheet" with that apostrophe: ' With ActiveSheet And you never got to the next error about having an "end with" without a "with". Remove that apostrophe and try it again. -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 07:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com