1. Deleting specific shapes (2000)

Hi All,

I'm working on a spreadsheet that has lots of used cells across several sheets. I'll define a range of cells, called mycells on a given sheet, that I want to do something special if a cell in the range changes. If the cell that changes is not in the range, I don't care and am finished. I know how to determine if a changed cell is in mycells in VBA using Union in a Worksheet Change event for the sheet containing mycells. For discussion, lets use cell B5 as one of the cells in mycells.

The range of cells can take on a value of 1-5 and NR. First problem: I'm not sure if B5, when it changes, is a result of a direct entry of a constant (ie, 1-5 or NR) into B5 or as a result of a formula in B5. The first case is easy - the Worksheet change event will execute and using Union I can determine if B5 (or any other cell in mycells) was the cell that changed. But I may also have to allow B5 changing as a result of one of the cells in its formula also changing. How would this be done?

Assuming I can determine that B5 changed, I then want to insert a picture based on the value of 1-5. This is basically those type of product reviews where you have a circle with different shading depending on the rating in the category. For example, if the product was excellent or 1, you want a solid black circle; if it was good or 2, you want a solid circle except for an open white circle in the middle; etc.

Assume I have jpeg's of the different shapes corresponding to the 1-5 ratings in a known folder.

Based on some code from the Lounge (probably Legare's but apologies if it was someone else's), I know how to insert a picture in VBA at a given cell and center it or stretch it to fit the cell. Using a Select Case, I can now call the routine that actually positions the picture with the path to the correct jpeg.

Two cases as to where the picture is placed:
- in cell B5 probably covering the value
- in a cell at a given offset from B5 on the same sheet so that you can still see B5; perhaps this picture would even go on another sheet (so that the cells on the other sheet can be pasted into powerpoint)

I know how to put the picture in either place.

Now here comes the tough part: suppose I want to change the value in B5 (directly, or through some predecessor cell in B5's formula if that can be done). Is there a way to just delete the picture that was in B5 (or at the given offset) and not delete all the others associated with cells in mycells? I know that the shape will have been given a name such as "Picture 22" when it was added.

I know that I can delete all the shapes in a sheet (some other code from the Lounge). I could then cycle thru all the cells in mycells and redo the pictures, although it was only B5 that changed. Not desirable since mycells would probably have 100-200 cells in it but livable if no other way.

I also assume that there is no property of Cells that would tell me if there's a shape on top of the cell and what its name is. If there is such a property, then I think I'm home free so let me know. So I'm thinking of an alternative along the following lines.

Would it be feasible to change the name of the picture when it was added to some known name and use this as a basis for deleting just the picture I want. For example, if "Picture 22" is the name of the picture when B5 first gets its value, perhaps I could change this to "Picture \$B\$5" where, obviously, I'm changing the random numeric (as far as I'm concerned) to the cell address (regardless of whether the picture was stored in B5 or at some offset). If this is doable, then I think I could construct the name as

<pre>shname = "Picture " & Cells(2, 1).Address
</pre>

except that I'd want the cell that caused the worksheet change to fire. Can I use "Picture " & Target.Address?

Then I want to delete it with a statement like

<pre>activesheet.shapes(shname).delete
</pre>

I assume I'd want an On Error Resume before the above in case it doesn't exist (eg, the first time B5 gets a value).

Then I'd want to turn error checking back on (know how) and insert the new picture based on B5's value wherever I want it (B5 or offset) but rename the picture's name to "Picture \$B\$5". I assume this would be pretty straightforward given the construction of the name for deleting above.

I certainly don't want to add the new picture on top of the old since that would make my file bigger.

So I think I'm just about there but perhaps could just use a few little shoves to get over the top for some of the open questions raised above. TIA.

Fred

2. Re: Deleting specific shapes (2000)

This is not exactly what you asked for but I think it might be close to what you want. It uses NO macros but uses formulas.
Col G has the values that can be changed to change the picures located in F. the "defined pictures" are in Col B next to their names (in A).
I used a picture of 1-9 for the values 1-9 and used a frown for <1 and smiley face for >9. These pictures could be another sheet if desired.
ColE could be eliminated (just a reference to the rangenames, it made it easier to create them.)

The only downside to this way is that every "variable" picture that you want has to be named and that name is an indirect reference. I made an intermediate column of the names (col F) but that actually could be part of the named indirect reference if desired. I thought it easier to do this way, though with a marcro it should be easy.

Steve

3. Re: Deleting specific shapes (2000)

Steve,

Thanks. This is good, better than good. I started off knowing some VBA pieces from previous posts and cobbled them together. But your approach is much easier to use.

Fred

4. Re: Deleting specific shapes (2000)

Steve,

I've looked at your example and need help understanding how it works (it sure does work).

I know I can delete Col E and things will still work. Tried it, did that, and it works.

I move the pictures out of Col B and things stop working. I know that I shouldn't but just wanted to see what happens if I do. I also tried putting col A and B on a separate sheet and that works too. THis is probably what I would do.

You imply the picture in Col B has the name in Col A. I know the Excel can pick up the name for a cell from an adjacent cell. But I didn't know that applies to pictures, which really float over the cell.

I certainly understand how the "text part" of Col F gets its value depending on what's in col G. But I guess I'm just missing how the picture that's part of col F gets put in there based on the "text part" of the col. Also why does the picture show up right aligned as opposed to anything else. Lastly, I note that I can move the picture out of col F to some other location, change col G, and the picture updates.

I must be missing some link - literally and figuratively.

Thanks.

Fred

5. Re: Deleting specific shapes (2000)

The "picture" is the cells in col B. Whatever (for example) is in Cell B1 will display when you enter a value >9. What I PUT in cell B1 was a picture of a smiley face.

If you widen the col width of B you will notice your pictures will change size (they are LINKED). If you change the background color, formatting of text, etc it will all be reflected.

If you change the SIZE of the pictures in Col B so they extend OUTSIDE the cell, the linked pictures will only show what is INSIDE the cell.

i hope this clarifies things.

Steve

6. Re: Deleting specific shapes (2000)

Hopefully, Steve's solution will work for you, it's a lot easier. If not, the naming trick that you outlined is the way to go. I try to always name any shapes that I create: makes it easier to debug. Remember that the Add method returns an object, so you can set the name in a with block:
.Name = shName
End With</pre>

Don't do any of those selects that the Macro recorder gives you.
HTH --Sam

7. Re: Deleting specific shapes (2000)

Steve,

whatever side of the brain deals with pictures, my never existed. I'm a numbers man. Now try using pictures in Excel and I'm in trouble.

The part I don't get is:

How?

Or put it another way: suppose I wanted to add a very happy face if the value in col G were greater than 100.

- I can add another value called "tickled pink" in A12 and find some corresponding face to put into B12 (were these inserted from somewhere?). I can insert a name called "tickled pink" to refer to B12. I still don't understand why the name refers to the picture since it floats over the cell. I did try stretching col B and adding text say in B1. I changed G1 to be 11 and saw that F1 reflected the proper face plus the text I put into B1. Is this new in Excel 2000 - pretty sure I didn't do this in 97 (or maybe it was that deficient side of my brain). This is not the main problem but would like to understand this.

- I can change the value in G1 to be 102. I do understand how to change the formula in F1 so it shows "tickled pink".

- I do understand that the A in E1 refers, via INDIRECT, to whatever is in F1. But this doesn't seem to be relevant, since you said, and I confirmed, that I could delete the values in col E and everything still works. (By the way, why did you use "C_" in E3?). So I don't need to do anything for this.

- what I still don't understand is how to get the picture that is part of F1. Or back to my original question: how are they linked?

thks

fred

8. Re: Deleting specific shapes (2000)

1) You have a set of "master pictures". These are the cells/ranges which CONTAIN the pictures. In my example they are labeled Good, Bad, Value1 - Value9 and they refer to cells B1 thru B11 respectively. So if you reference "Good" you are refering to Cell B1. Another way is to say B1 is named "Good". The Masters do NOT have to refer to single cells. I created a range named "TickledPink" which refers to B12:C16.

In each of the cells B1-B11 I placed a picture. These happen to be the "faceID"s that office uses to add onto toolbar buttons. They could be bmp, jpg, etc it doesn't matter. You just need to place it in a cell or range of cells. the col width and row height of the referenced cells are critical. This techniques uses "Pictures" of the cells (with whatever formatting it has). I enlarged the smiley and made it PINK (you can edit them via toolbar customize).

In Cells G1 thru G12 contain cells which will contain values or formulas which you want to refer to a picture. Depending on what these values are the picture will change. they can be anywhere on the sheet.

F1 thru F12 has the formula whose result will be one of the range names from the master picture list (good, bad, value1 - value9 or tickledpink in my example). This formula is (for G1):

Each of PICTURES you want to display is "linked" to range name (A-L, they can be ANYTHING, this was convenient, you can name them based on WHAT the value refers to: eg YTDProductionPic, YTDSafetyPic, etc. It doesn't matter.

[Aside: regarding your question: Why C_, Why not C? C (and R) are the only 1 letter range names NOT allowed. They are used to refer to Col/Row in RC nomenclature. You can also not have range names which match cell references (a1, t1, etc). Another quirk, some have discovered, is due to the R/C nomenclature is that names that START with an R or a C and the 2nd character is a number are not allowed EVEN if the rest are letters! R1xxx is a non-valid name!. i don't completely understand, it just is]

Now that we have MasterPictures that are named, we have formulas which results in displaying those names when referring to certain cells, and we have pictures which reference named ranges we need to define those named ranges!

If you look at the defined names for A-L, you will see that they refer to a formula(for A):
=INDIRECT(Sheet1!\$F\$1)

Therefore picture A will grab "whatever cell F1 is referring to". F1 in my example is "bad" since G1 <1 (G1 = 0) and thus picture named A will display a picture of the cell named Bad = Cell B2. B2 is empty except for a picture of frown.

If G1 is changed to 200. F1 will change to tickledpink (F1>100!) so that A will now display the range named tickledpink which refers to B12:C16 and contains a picture a large pink smiley! (NOTICE the size of the Picture CHANGES!! since the range is larger! All the pictures may be of different sizes if desired.

I hope that is more clear and I have answered all of your questions.

If not repost more questions and I will try to explain it better or use a different example.

Steve

9. Re: Deleting specific shapes (2000)

Steve,

Thanks alot. Got it working now.
Fred

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•