To some other auto shapes that we covered in this video so to help demonstrate this i've got a blank excel workbook setup and i've already created a new module and started.
A subroutine called draw lines to begin with drawing a line is a lot like drawing any other shape on a worksheet we need to add the line to the shapes collection of the sheet object.
To help with that let's start by declaring a variable i'm going to call mine sh and it's going to hold a reference to a shape object we can then set that variable to be.
Equal to and i'm going to refer to my sheet1 object using its code name and then refer to the shapes collection and use the add line method if i then open up some round brackets we.
Already obvious i've got a couple of basic diagrams set up so any straight line has just got two points and each of those two points requires two coordinates the distance from the left.
Hand edge of the sheet to the point and the distance from the top of the sheet to the point so we've got begin x and begin y and x and end y representing the four coordinates we need.
So if we wanted a straight vertical line then the values of the x coordinates would be the same if we wanted a straight horizontal line the coordinates of the y values would be.
The same so just to demonstrate how this works let's head back to the visual basic editor and i'm going to name these parameters explicitly on separate lines.
Just to help it help us read this later on so we'll say begin x colon equals i'm going to start it 10 from the left-hand edge of the sheet.
- SQL Server Programming Part 1 - Stored Procedure Basics
- How to Create Rocket Icon in Powerpoint PowerPoint Show
- Design A CSS Box Shadow Generator Using HTML, CSS & JS (Part 3) BlogSpot
And then on the next line we'll say - ID Card Make
Begin y and we'll say again colon equals and let's set this to be again 10 from the top of the sheet.And then we'll say index colon equals 20 and then end y colon equals 20 as well.
So this will give us a diagonal line sitting on the worksheet just to help the line stand out to make it a bit more obvious i'm going to change a couple of the basic formatting.
Properties i'm going to say sh dot line dot for color dot rgb equals i'll spell rgb correctly apologies for that equals rgb hot pink and then sh.line dot weight i'm going to.
Set that to be equal to t so having done that let's run the subroutine and we should see if we switch back to sheet 1 in the workbook we've got a nice small pink line sitting.
On the sheet now of course what dan wants to do is draw his line within a range on the worksheet and to do that we can make use of a few properties of a range so let's.
Say we want to draw our line diagonally across cell b2 cell b2 has got a left property which indicates the distance from the left of the sheet to the left of the cell a top property representing.
The distance from the top of the sheet to the top of the cell and then a width and a height property as well so we could make use of all those things if we switch back to the visual basic.
Editor so we could set the beginning x position to say range b2 dot left and we could set the begin y position to be range b2 dot top let's just do a quick bit of copying and pasting here to.
Save a bit of time
The end x position we could do this in a couple of ways i suppose we could say range c 2 dot left so draw the line up to the edge of cell c2.Alternatively we could say range b2 dot left plus range b2 dot width this seems like a bit of a more.
Elaborate way of doing it but it provides us with a couple of interesting possibilities a little later and then likewise for the height of the line or the sorry the the end position.
Of the line vertically we can refer to the range b2 dot top position plus range b2 dot height.
So having done all that if we now run the subroutine again we'll end up with a new line which starts in the top left hand corner of cell b2 and ends in the bottom right hand corner.
Just before we look at some other interesting possibilities i'm going to write a quick bit of code that deletes all the shapes on this worksheet otherwise things are going to get quite.
Messy with all our lines drawn on top of each other so heading back to the visual basic editor just after declaring that variable i'm going to write a for each loop to say for each essay gin sheet1.
Dot shapes i'm going to say sh dot delete and then next sh so we'll loop through the shapes collection deleting each one in turn.
Before drawing our new one so if we run this subroutine again now at this point we'll see the original line has disappeared and our second line also disappeared before drawing the new line.
In its place now let's say that we wanted to draw this line horizontally but starting halfway down the height of cell b2 we can do this fairly easily just using a.
Bit of basic arithmetic to divide the height of the cell by two so if we head back to the visual basic editor we can set the begin y position to be equal to range b2 dot top.
Plus range b2 dot height divided by two so i'm just going to quickly copy and paste here and i'll put this in a set of round brackets as well so range b2 dot height.
Divided by two we can then do the exact same thing for the end y position if we want a horizontal line then the y coordinates should be equal.
So having done that we can run the subroutine again and now our line is drawn horizontally halfway down the height of the cell that halfway point is calculated.
Relative to the current height of the cell at the point the line is drawn so if we change the height of row two there just by clicking and dragging in the left hand side when we run the.
Subroutine again we'll see that the halfway position is recalculated so the line is drawn halfway down the cell again just to demonstrate how lines respond to.
Changes in the width and height of cells i want to redraw this line so it starts in the top left hand corner of cell b2 and ends in the bottom right hand corner of cell c3.
So let's head back to the visual basic editor i'm going to change the begin y position to begin at the top of cell b2 then the end x position is going to be range c3.
Dot left plus range c3 dot width and the end y position is going to be range c3 dot top plus range c3 dot height without dividing anything by two there.
So once we've done that we can run the subroutine again and now the line is drawn diagonally across those two cells by default a line isn't drawn in a fixed.
Position on the worksheet it can both move and resize itself according to changes in other cells so for example if anything above or to the left of the line changes in terms of its width or.
Its height you'll see that the line moves in response if any cell widths or heights change within the boundary of the shape up to and.
Including the end of the shape then this shape will resize itself so you can see here the shape is resizing so that it still stays fixed to the bottom right hand corner of cell c3.
And this includes the extreme end of the line as well if i change the end of cell c3 or the bottom of row 3 there you'll see that line resizes itself too now you can influence that by changing.
The placement property of the shape if i head back to the visual basic editor after i've changed some formatting properties let's say s h dot placement you'll see there are three possible.
Values so there's free floating now this will ignore any moving and resizing options so this will draw the line in a fixed position on the page if we run that subroutine now head.
Back to the visual basic editor you'll see that the line doesn't move at all in response to any changes made to any cells at all if i head back to the visual basic.
Editor one of the other options there was to set this to excel move and move only so if i set that to move and then run the subroutine again.
We'll see that if anything above or to the left of the line changes the line will move itself but it won't respond to changes within the boundary of the line so it.
