I have this code on a check box
Private Sub chkboxE4NA_Click()
ActiveSheet.Unprotect
'Draws a line accross both pages in Worksheet E4 and directs the user to
Worksheet E5
ActiveSheet.Shapes.AddLine(0#, 1.5, 543.75, 713.25).Select
Selection.ShapeRange.Line.Weight = 2.25
Selection.ShapeRange.Flip msoFlipHorizontal
ActiveSheet.Shapes.AddLine(546.75, 1.5, 1073.25, 714#).Select
Selection.ShapeRange.Line.Weight = 2.25
Selection.ShapeRange.Flip msoFlipHorizontal
'Checks the checkbox in page 2 to indicate this sheet also does N/A
chkE4NA1.Value = True
'this places the protection back on the worksheet
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
'This takes the user to E5 since worksheet E4 does not apply
Sheets("E5").Select
End Sub

I would like to add an option for the user to delete the diagonal lines
created.
I can't seem to come up with how to do this. If I uncheck the chkbox, it
adds a new line ontop of the one already there. I would like the unchecking
action to delete the lines.
This is to save the user time and to make things look neat when printing the
workbook. So adding these lines corssing out the sections is a nice
enhancement to my workbook.

Thank you

If there is not other shape on the sheets then add similar code to the
uncheck event

For Each shp In ActiveSheet.Shapes
shp.Delete
Next

"Memphis" wrote:

I have this code on a check box
Private Sub chkboxE4NA_Click()
ActiveSheet.Unprotect
'Draws a line accross both pages in Worksheet E4 and directs the user to
Worksheet E5
ActiveSheet.Shapes.AddLine(0#, 1.5, 543.75, 713.25).Select
Selection.ShapeRange.Line.Weight = 2.25
Selection.ShapeRange.Flip msoFlipHorizontal
ActiveSheet.Shapes.AddLine(546.75, 1.5, 1073.25, 714#).Select
Selection.ShapeRange.Line.Weight = 2.25
Selection.ShapeRange.Flip msoFlipHorizontal
'Checks the checkbox in page 2 to indicate this sheet also does N/A
chkE4NA1.Value = True
'this places the protection back on the worksheet
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
'This takes the user to E5 since worksheet E4 does not apply
Sheets("E5").Select
End Sub

I would like to add an option for the user to delete the diagonal lines
created.
I can't seem to come up with how to do this. If I uncheck the chkbox, it
adds a new line ontop of the one already there. I would like the unchecking
action to delete the lines.
This is to save the user time and to make things look neat when printing the
workbook. So adding these lines corssing out the sections is a nice
enhancement to my workbook.

Thank you

Thank you Sheelo,
But i do have including the CheckBox many other option buttons created from
the Control Toolbox. i ran a test and it deletes the existing option buttons
and the chkboxE4NA.
I also have grouped many of the option buttons since they are Yes and No
answers.

What to do?

Thanks

"Sheeloo" wrote:

If there is not other shape on the sheets then add similar code to the
uncheck event

For Each shp In ActiveSheet.Shapes
shp.Delete
Next

Sheelo,
i just realized i posted the question in two separate places.. At first i
got an error when i tried to post this question, and did not know if it made
it in. So I posted the question under programing.
I received a reply from JLGWhiz and his code works.

Here it is:
For Each shp In Sheets(1).Shapes '<<<validate sheet nbr.
If shp.Type = 9 Then
shp.Delete
End If
Next
End If

this code did the trick.

Take a look at Ron de Bruin's site:
http://www.rondebruin.nl/controlsobjectsworksheet.htm

You'll see how he limits what he deletes by looking at the shape.type.

Option Explicit
Sub testme()
Dim shp As Shape
For Each shp In Worksheets("sheet1").Shapes
If shp.Type = msoLine Then
shp.Delete
End If
Next shp
End Sub

If there aren't too many lines, you may be able to use:

Sub testme2()
Worksheets("Sheet1").Lines.Delete
End Sub

If you decide you want to keep some lines and delete others, you may want to
give a name to each when you add them. Using a nice naming convention may make
it easier:

Option Explicit
Sub testme()
Dim shp As Shape
For Each shp In Worksheets("sheet1").Shapes
if lcase(shp.name) like lcase("deletemelater*") then
shp.Delete
End If
Next shp
End Sub

Memphis wrote:

Thank you Sheelo,
But i do have including the CheckBox many other option buttons created from
the Control Toolbox. i ran a test and it deletes the existing option buttons
and the chkboxE4NA.
I also have grouped many of the option buttons since they are Yes and No
answers.

What to do?

Thanks

"Sheeloo" wrote:

If there is not other shape on the sheets then add similar code to the
uncheck event

For Each shp In ActiveSheet.Shapes
shp.Delete
Next


--

Dave Peterson

Try (if you don't have other lines)
For Each sh In myDocument.Shapes
If sh.Type = msoLine Then
sh.Delete
End If
Nex

Also at the time of adding the sh.ape store its Index ( by using
shapes.count property and then use that index to delete...

"Memphis" wrote:

Thank you Sheelo,
But i do have including the CheckBox many other option buttons created from
the Control Toolbox. i ran a test and it deletes the existing option buttons
and the chkboxE4NA.
I also have grouped many of the option buttons since they are Yes and No
answers.

What to do?

Thanks

"Sheeloo" wrote:

If there is not other shape on the sheets then add similar code to the
uncheck event

For Each shp In ActiveSheet.Shapes
shp.Delete
Next

Glad it worked for you.
Somehow the following line did not showup in my code
Set myDocument = Worksheets(1)

btw msoLine is the constant for 9...

"Memphis" wrote:

Sheelo,
i just realized i posted the question in two separate places.. At first i
got an error when i tried to post this question, and did not know if it made
it in. So I posted the question under programing.
I received a reply from JLGWhiz and his code works.

Here it is:
For Each shp In Sheets(1).Shapes '<<<validate sheet nbr.
If shp.Type = 9 Then
shp.Delete
End If
Next
End If

this code did the trick.