Draw an arrow equal the size of the selected cells

I want to select some cells (like A2-A20) and have it draw an arrow down the selected cells. Tried a few different things, none of which worked...

What did you try?

Did you try recording a macro to see how the end points of a line are specified?

Did you look at the Top, Left, Height and Width properties of the Range object?

I tried recording. the recording didn't seem to capture the drawing of the arrow at all in the code.

I also tried something like this:

Please Login or Register  to view this content.

and no go.

That's not the code you got when you recorded adding an arrow, was it? It's not what I get ...

Originally Posted by shg

That's not the code you got when you recorded adding an arrow, was it? It's not what I get ...

I didn't get ANY code when adding an arrow...

What version of Excel?

Here's what I get:

Please Login or Register  to view this content.

Which I edited to:

Please Login or Register  to view this content.

Hello koticphreak,

This macro will add a Down Arrow to the selected range of cells, center it and make it yellow. The range is restricted to being one column wide.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Originally Posted by Leith Ross

Hello koticphreak,

This macro will add a Down Arrow to the selected range of cells, center it and make it yellow. The range is restricted to being one column wide.

CODE

Sincerely,
Leith Ross

This works great, except I'm having a bit of trouble doing 2 things with it that I thought would be fairly easy but... not so much. I would now like to put a String in the first cell of the selection and have the arrow not start until the next cell.

Also, I'm trying to figure out how to change the arrow to have an Open Arrow end type. I figure it has to do with the DownArrow.Line.Style function, but I can't find it.

Hello koticphreak,

I am not sure what you mean by "open ended", but here is the change to start the arrow below the first cell.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Originally Posted by Leith Ross

Hello koticphreak,

I am not sure what you mean by "open ended", but here is the change to start the arrow below the first cell.

CODE

Sincerely,
Leith Ross

You can have a closed aarow or an open one, its an end style, could be something only in 2007, not sure.

Originally Posted by Leith Ross

Hello koticphreak,

I am not sure what you mean by "open ended", but here is the change to start the arrow below the first cell.

CODE

Sincerely,
Leith Ross

Im still having trouble trying to select the first cell of the range to input a string into...

Originally Posted by Andy Pope

post an example workbook with the arrow position and formatted as requried.

Attached is what I'd like. Thanks!

Thanks! That works great for changing the arrow to be exactly how I wanted, but how would I modify that to put a characters in the first cell of the selection?

If someone could just point me in the right direction to select the first cell in the range, I could figure out the rest on my own... THANKS!

this is what i added to it, but its not working...

Please Login or Register  to view this content.

What am I doing wrong to make it select the first cell and type in it?

Originally Posted by Andy Pope

You would add this line before resizing the range for the arrow.

Please Login or Register  to view this content.

wow, that was SO MUCH easier than what I was trying! thanks!!!

I'm trying to change this to draw the arrow across(horizontally rather than vertically. I tried doing it but failed miserably... little help? Thanks!

This is what i tried:

Please Login or Register  to view this content.