G
Guest
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.
Thanks.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
T
Tom Ogilvy
What kind of checkboxes - control toolbox toolbar or forms toolbar
--
Regards,
Tom Ogilvy
this??
--
Regards,
Tom Ogilvy
then I want to delete/collapse the rows. How do I create a macro to doCindy said:I have created a spreadsheet with check boxes. If the box is not checked,
this??
G
Guest
Forms toolbar.
Thanks for the reply.
Cindy Johnson
----- Tom Ogilvy wrote: -----
What kind of checkboxes - control toolbox toolbar or forms toolbar
--
Regards,
Tom Ogilvy
this??
Thanks for the reply.
Cindy Johnson
----- Tom Ogilvy wrote: -----
What kind of checkboxes - control toolbox toolbar or forms toolbar
--
Regards,
Tom Ogilvy
then I want to delete/collapse the rows. How do I create a macro to doCindy said:I have created a spreadsheet with check boxes. If the box is not checked,
this??
D
Dave Peterson
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
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
G
Guest
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
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
G
Guest
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
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
D
Dave Peterson
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.)
"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.)
G
Guest
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
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
D
Dave Peterson
Did you include that:
with Activesheet
the leading dots means that it's associated with a "with" statement--the one
directly preceding that line.
with Activesheet
the leading dots means that it's associated with a "with" statement--the one
directly preceding that line.
G
Guest
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 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
D
Dave Peterson
It worked ok for me.
I guess the next questions are:
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.
I guess the next questions are:
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.
T
Tom Ogilvy
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
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
G
Guest
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 are:
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.
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 are:
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.
D
Dave Peterson
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 ....)
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 ....)
G
Guest
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 ....)
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 ....)
D
Dave Peterson
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.
' 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.
G
Guest
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
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
D
Dave Peterson
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
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
G
Guest
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
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
D
Dave Peterson
Well, glad you got part of it working.
Cindy said: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