Draw line of defined length

S

Subodh

Hi,
I want to draw a line of length equal to value of any
cell value.
For example, if the cell value is 10 its length should be 10 units and
it should
be changed to 15 if the cell value is 15.
Further, if possible, I would like to fix the point for the start of
the line (ie from where the line
will start). This is because I want to draw a quadrilater , say a
polygon with four sides,
two should be parallel to each other and the other should be inclined.
We have the lengths
for all the sides. So, when we change the dimensions of the lines they
should change
and still the polygon should be closed.
Any help in this will be appreciated.
Thanks in advance.
 
L

lifescholar

Hi,
I want to draw a line of length equal to value of any
cell value.
For example, if the cell value is 10 its length should be 10 units and
it should
be changed to 15 if the cell value is 15.
Further, if possible, I would like to fix the point for the start of
the line (ie from where the line
will start). This is because I want to draw a quadrilater , say a
polygon with four sides,
two should be parallel to each other and the other should be inclined.
We have the lengths
for all the sides. So, when we change the dimensions of the lines they
should change
and still the polygon should be closed.
Any help in this will be appreciated.
Thanks in advance.
Click to expand...

Why not use the parallelogram shape in Excel, rather than trying to
draw the shape with lines? Something like:

Sub Parallelogram()
ActiveSheet.Shapes.AddShape(msoShapeParallelogram, 420.75, 76.5,
177.75, 104.25).Select
Selection.ShapeRange.Fill.Visible = msoFalse
End Sub

The numbers are for (in order) the Left and Top positions on the
sheet, then the Width and Height of the shape, which could be pulled
from a cell, in which case you would substitute the values as follows:
Range("A1").Value
 
S

Subodh

Why not use the parallelogram shape in Excel, rather than trying to
draw the shape with lines? Something like:
Click to expand...
Thanks.
THis works.
What about if i need to draw just a line (not straight but inclined)
or a triangle ? Is there any way around for that ??
 
L

lifescholar

Thanks.
THis works.
What about if i need to draw just a line (not straight but inclined)
or a triangle ? Is there any way around for that ??
Click to expand...

Check the online help for AddShape. This should list all of the shape
types (i.e. the other ones besides msoShapeParallelogram), of which
line and triangle should be examples.
 
C

Cimjet

Hi
Check this out.. just play with the numbers to change the arc and length
Sub DrawArc()
With ActiveSheet.Arcs.Add(10, 10, 200, 200)
With .Border
.LineStyle = xlContinuous
.Weight = xlThin
End With
End With
End Sub
===========
Triangle with all the bells and whistle, this one adjustable with size set in
cells E3 and E4
Sub Make_Triangles()
Height_size = Range("E3").Value
Width_size = Range("E4").Value
ActiveSheet.Shapes.AddShape(msoShapeIsoscelesTriangle, 0, 200#, Width_size,
Height_size).Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(180, 180, 120)
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.RGB = RGB(10, 10, 10)
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.Fill.PresetTextured msoTextureWhiteMarble

End Sub
========
Different type of triangle, just change the line in the script above.
ActiveSheet.Shapes.AddShape(msoShapeRightTriangle, 0, 300#, Width_size,
Height_size).Select
================
HTH
Cimjet

Why not use the parallelogram shape in Excel, rather than trying to
draw the shape with lines? Something like:
Click to expand...
Thanks.
THis works.
What about if i need to draw just a line (not straight but inclined)
or a triangle ? Is there any way around for that ??
 
S

Subodh

Hi
Check this out.. just play with the numbers to change the arc and length
Sub DrawArc()
    With ActiveSheet.Arcs.Add(10, 10, 200, 200)
        With .Border
            .LineStyle = xlContinuous
            .Weight = xlThin
        End With
    End With
End Sub
===========
Triangle with all the bells and whistle, this one adjustable with size set in
cells E3 and  E4
Sub Make_Triangles()
    Height_size = Range("E3").Value
    Width_size = Range("E4").Value
     ActiveSheet.Shapes.AddShape(msoShapeIsoscelesTriangle, 0, 200#, Width_size,
Height_size).Select
       Selection.ShapeRange.Fill.Visible = msoTrue
    Selection.ShapeRange.Fill.Solid
    Selection.ShapeRange.Fill.ForeColor.RGB = RGB(180, 180, 120)
    Selection.ShapeRange.Fill.Transparency = 0#
    Selection.ShapeRange.Line.Weight = 0.75
    Selection.ShapeRange.Line.DashStyle = msoLineSolid
    Selection.ShapeRange.Line.Style = msoLineSingle
    Selection.ShapeRange.Line.Transparency = 0#
    Selection.ShapeRange.Line.Visible = msoTrue
    Selection.ShapeRange.Line.ForeColor.RGB = RGB(10, 10, 10)
    Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
   Selection.ShapeRange.Fill.PresetTextured msoTextureWhiteMarble

End Sub
========
Different type of triangle, just change the line in the script above.
ActiveSheet.Shapes.AddShape(msoShapeRightTriangle, 0, 300#, Width_size,
Height_size).Select
================
HTH
Cimjet





Thanks.
THis works.
What about if i need to draw just a line (not straight but inclined)
or a triangle ? Is there any way around for that ??






- Show quoted text -- Hide quoted text -

- Show quoted text -
Click to expand...

This is working fine. But when i try to get the line or any shape, I
have made trial to update
the shape each time the value changes.
But, the problem is that the shape is there alwasy. So, I want the VBA
to remember the previous one
and delete it beofre drawing it. So, it doesn't override
Any suggestions.
 
C

Cimjet

Hi
I'm not sure I understand your question.
if you want to delete the previous shape so they don't overlap. Just add this
line
"With ActiveSheet.Arcs.Delete
End With "
like sample below.
Sub DrawArc()
With ActiveSheet.Arcs.Delete
End With
With ActiveSheet.Arcs.Add(Range("D1"), Range("E1"), 200, 200)
With .Border
.LineStyle = xlContinuous
.Weight = xlThin
End With
End With
End Sub
HTH
Cimjet

Hi
Check this out.. just play with the numbers to change the arc and length
Sub DrawArc()
With ActiveSheet.Arcs.Add(10, 10, 200, 200)
With .Border
.LineStyle = xlContinuous
.Weight = xlThin
End With
End With
End Sub
===========
Triangle with all the bells and whistle, this one adjustable with size set in
cells E3 and E4
Sub Make_Triangles()
Height_size = Range("E3").Value
Width_size = Range("E4").Value
ActiveSheet.Shapes.AddShape(msoShapeIsoscelesTriangle, 0, 200#, Width_size,
Height_size).Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(180, 180, 120)
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.RGB = RGB(10, 10, 10)
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.Fill.PresetTextured msoTextureWhiteMarble

End Sub
========
Different type of triangle, just change the line in the script above.
ActiveSheet.Shapes.AddShape(msoShapeRightTriangle, 0, 300#, Width_size,
Height_size).Select
================
HTH
Cimjet





Thanks.
THis works.
What about if i need to draw just a line (not straight but inclined)
or a triangle ? Is there any way around for that ??






- Show quoted text -- Hide quoted text -

- Show quoted text -
Click to expand...

This is working fine. But when i try to get the line or any shape, I
have made trial to update
the shape each time the value changes.
But, the problem is that the shape is there alwasy. So, I want the VBA
to remember the previous one
and delete it beofre drawing it. So, it doesn't override
Any suggestions.
 
S

Subodh

Hi
I'm not sure I understand your question.
if you want to delete the previous shape so they don't overlap. Just add this
line
 "With ActiveSheet.Arcs.Delete
 End With "
like sample below.
Sub DrawArc()
 With ActiveSheet.Arcs.Delete
 End With
        With ActiveSheet.Arcs.Add(Range("D1"), Range("E1"), 200, 200)
        With .Border
            .LineStyle = xlContinuous
            .Weight = xlThin
        End With
    End With
End Sub
HTH
Cimjet










This is working fine. But when i try to get the line or any shape, I
have made trial to update
the shape each time the value changes.
But, the problem is that the shape is there alwasy. So, I want the VBA
to remember the previous one
and delete it beofre drawing it. So, it doesn't override
Any suggestions.- Hide quoted text -

- Show quoted text -
Click to expand...

The actual problem is because I use for example different similar
subroutine as follows

Sub1 ()
............ draw line 1
end sub

sub2 ()
.............. draw line 2
end sub
.........
sub10 ()
................ draw line 10
end sub
Now, I want Excel to remember every line drawn, so
that I can delete any one or all the line drawn as required.
ie. Lets say, for a particular conditon, I want to delete all the
lines except line 1 or I want all line to remain the same and just
increase length of the line 1 or change its position.
Hope this will reflect the problem.
 
C

Cimjet

Hi
Find below a link to a sample file.
I needed help getting the name of the Arc but everything is working ok
Every time an Arc is created, it copies the coordinate with the Arc name.
If you need to delete one, you will have to delete it manually.
If you want to delete all the Arcs, you have the Delete All button, it also
deletes the coordinate.
I don't know if this can help you, if you need information about the file, post
back
LINK:
http://cjoint.com/?AIqveD4xkyg

HTH
Cimjet

Hi
I'm not sure I understand your question.
if you want to delete the previous shape so they don't overlap. Just add this
line
"With ActiveSheet.Arcs.Delete
End With "
like sample below.
Sub DrawArc()
With ActiveSheet.Arcs.Delete
End With
With ActiveSheet.Arcs.Add(Range("D1"), Range("E1"), 200, 200)
With .Border
.LineStyle = xlContinuous
.Weight = xlThin
End With
End With
End Sub
HTH
Cimjet










This is working fine. But when i try to get the line or any shape, I
have made trial to update
the shape each time the value changes.
But, the problem is that the shape is there alwasy. So, I want the VBA
to remember the previous one
and delete it beofre drawing it. So, it doesn't override
Any suggestions.- Hide quoted text -

- Show quoted text -
Click to expand...

The actual problem is because I use for example different similar
subroutine as follows

Sub1 ()
............ draw line 1
end sub

sub2 ()
.............. draw line 2
end sub
.........
sub10 ()
................ draw line 10
end sub
Now, I want Excel to remember every line drawn, so
that I can delete any one or all the line drawn as required.
ie. Lets say, for a particular conditon, I want to delete all the
lines except line 1 or I want all line to remain the same and just
increase length of the line 1 or change its position.
Hope this will reflect the problem.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

length of line depending on the value of the cell 13
Adjusting height of text cells 3
Measure length of an AutoShape Line? 6
Returning Position Points from the currently selected cell 2
draw arrows using formula 2
Adding a Line using VBA 2
Macro to graph (or draw) a vertical line on the chart 7
Length of integer 4

Top