Macro for creating image border upon being clicked?

Hello,

I have an existing spreadsheet with macros assigned to a bunch of plain, unformatted shapes that move data around in the spreadsheets when they are clicked.

I now need (hopefully) to find a way so that when the shape is clicked, not only does it retain its current functionality, but it adds a thick visible colored border to it's self based on the fill color of a specific cell.

For example...
If i had a red square, and blue square, and a orange square side by side, clicking on the red square would change things so the red square now has a 5 pixel lime green border around it because cell C3 is lime green. If I then clicked the orange square, the red square would loose its border, and the orange square would now have a 5 pixel sky blue border around it because cell D3 is sky blue.

Any help is greatly appreciated.
(Telling me it can't be done works too)


-Aaron

hi aaron

the following will toggle the line visibility for the shape named "Oval 1" on sheet1

Please Login or Register  to view this content.

(To find/set shape names, highlight them and look in the name box to the left of the formula bar)

You can set colours based on cell colours by using

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

the latter will pick the scheme colour closest matching the rgb value specified.

hopefully you can take it from there

Thank you a lot blackworx,

Now when I click a shape, it gets the appropriate outline.
Though the outline doesn't end until I click the object a second time.
And I can't figure out how to have the outline on the first object disapear once the second object is clicked.

Any insight is appreciated,

Thanks =]


-Aaron

A follow up question,

the line:
.Line.ForeColor.SchemeColor = .Parent.Range(Mid(.Name, 4)).Interior.ColorIndex + 7

instead of pointing to a cell based on the shape's name, i need it to point to a cell on a seperate sheet. I dont quite know how to go about that. =/

I tried the following and it doesn't seem to work?
.Line.ForeColor.SchemeColor = .Parent.Range(Sheets(1).Cells(107, 3)).Interior.ColorIndex + 7

Hi Aaron,

The ".Parent" part of the original code tells Excel to look at whatever sheet the shape belongs to, so what you want is to replace that with a reference to the required sheet.

So, instead of:

Please Login or Register  to view this content.

we want something like:

Please Login or Register  to view this content.

(This assumes that all the cells you want to refer to are contained on the same sheet.)

Sounds good,

Unfortunately I am looking up data across three+ seperate sheets for the colour data. Currently using a custom function which is a variant of vlookup that scans all sheets instead of just one sheet for most of my search requirements.

To be clear, in your function ' Range(Mid(.Name, 4)) ' is just referring to the naming convention you used on your shapes to include the cell range to which they refer? Asking to ensure that I could replace it with something like ' Range("c4") ', or more likely something like ' Range(initializedVariable) '.

Hi Aaron, that's right. In my example, you could pass the required range like so:

Please Login or Register  to view this content.

The call would look like:

Please Login or Register  to view this content.