Display arrow based on cell value.

[WaterSkiExcel]

I have a worksheet with a column of various sized and colored arrows (AutoShapes) in column T (T5:T19). In column S (S5:S19) I have corresponding numbers.

I want to lookup the numbers in column E from the table (S5:T19) and return the corresponding arrow shape in column F.

Is this possible?

Thanks,

Ryan

 

[Damon Ostrander]

Hi WaterSkiExcel,

Yes, it is possible. There are three possible approaches:

1. have the code look for the shape in the cell in column T that corresponds to the number of interest. This involves having the code find the desired arrow by looping through the shapes until one is found that has its upper-left corner within the row containing the number.

2. add a data column (possibly U?) in which you place the name (the shape name) of the arrow shape corresponding to the shape (arrow) in column T. If there are a lot of arrows, a simple macro could be written to put the names in column U, taking the tedium out of it.

3. name each arrow shape object using the numbers in column S. Each arrow could be then named something like "arrow_nnnnnn", where nnnnnn is a column S number. A macro could also be written to rename all the arrows automatically, again removing the tedium.

I recommend option 3 as it doesn't require the extra column, and is very efficient at looking up the right arrow since it is just a matter in VBA of referring to it by its text string name. Would you like the code for this?

Damon

 

[WaterSkiExcel]

Thanks for the reply Damon, option 3 sounds great. Could you possibly send me some code? I'm still pretty new to VBA.

Thanks a bunch,

Ryan