Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Name that Shape (97+)

    Hi,

    I have a series of 5 pictures on a sheet called "Grade Pictures". On other sheets called Arch, Service, etc, I have letter grades for various evaluations of vendors and different categories (about 360 vendor-category combinations per sheet). Depending on the letter grade, I want to paste the appropriate picture from the Grade Pictures sheet.

    I've set up the Grade Pictures sheet with 5 jpegs in B1 thru B5. In A1 thru A5, I have "GradeA", "GradeB", etc.

    I can use code to do the pasting (another thread about a week ago had a generalization of the problem that wasn't practicable). But because of the way the paste gets done (some pastes don't look like the original pictures but it seems random), I have to paste special as a JPEG. No problem. However, once having done the paste special in VBA, I need to access the newly-created picture. How do I know the name of this object (like "Picture 245")? I need to be able to set a pointer to it so I can set the properties of the picture for purposes of positioning it.

    If there's an approach other than copy/paste special, let me know. However, the object.add/insert method didn't work. The picture didn't come out correctly.

    TIA

    Fred

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Name that Shape (97+)

    As soon as you paste the picture assing Selection.Name to a string variable as follows :<pre> ActiveSheet.PasteSpecial Format:="Picture (JPEG)"
    strNewPic = Selection.Name</pre>

    Andrew C

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Name that Shape (97+)

    Andrew,

    Thanks a lot. I kind of guessed that was my only choice.

    Fred

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Name that Shape (97+)

    I think the method discussed in here <post#=183791>post 183791</post#> should work for you.

    If that is the method that you think is impractical, I think that point could be argued.
    The method you want (if I undersand correctly) is to have a copy of 5 master pictures and with VB you will copy/paste one of them by the appropriate information on another sheet. Your problem was in knowing the name of the picture you paste-special.

    My method NAMES the picture first (as a rangename) it could be based on a vendor name or a vendor/category combo name or anything else.

    I just thought I would mention it again. It is a "neat technique": it involves NO VB, and is LIVE: if info is changed in the database, the picture will change accordingly. It also will change size automatically if the "pictures" are different sizes.

    Steve

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Name that Shape (97+)

    Steve,

    This was actually for someone who works for me. When he came to me with the problem of replacing grades with circles, I knew I had seen some VBA postings that touched on most of the elements of a solution. Hence I proceeded in that direction. However, he had already done this manually and thought he was done. So I really didn't finish off what I was working on.

    I had also originated the other thread you mentioned about deleting shapes because of one specific issue with what I had previously gathered from those other threads. When he said he was finished (so he thought), I stopped working on my approach and your approach.

    But I thought the method in your post was pretty neat also, as I had mentioned.

    Guess what - he wasn't finished. Some of the grades got changed as more information was found. He came back and we looked at your solution and what I had done with VBA.

    Naming the 5 shapes is obviously a trivial thing to do. But he had to apply those 5 shapes to 360 grades per worksheet for 2 or 3 sheets - about 1,000 in all. So imagine the effort he expended in the 1st pass doing this manually! On each sheet, there were 3 ranges of 12 cols by 10 rows. Given the workbook was finished, we didn't see a practical way of getting the second set of names (A, B, C_, etc) onto the sheet. If he were starting from scratch, your way would be the way to go.

    So I agreed to finish off the VBA approach and just had the 1 question posted on this thread.

    Thanks for the help.

    Fred

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Name that Shape (97+)

    I would have to think about the details, but you could create a macro to define the range names (they don't have to be A, B, C_, etc, they could be vendor names) and set the "refersto" based on data in a row so it would be only a minor inconvenience not a major pain.

    You could create a column of range names, and a column of cells that give you (through a formula) the name of the "range" you want to link. The macro would scroll through this list, ADDing the name to the name collection and making the "refersto" = "=indirect("cell address for the formula")"

    It would be short code, just loop thru the selection.

    Steve

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Name that Shape (97+)

    Steve,

    When my colleague came to me and agreed with my observation (and yours) that we would have to name the ranges to use your approach, I had already done 80% of the VBA for pasting the circles. At that point, we made one of those "executive" decisions - finish the other 20% of the work on my approach. Then I came across the problem of "what is the name of the object after the paste special" so I could adjust its attributes. I took a guess as Andrew suggested and we were home free.

    We recognized that we could write a macro to name the 360 cells and we're into VBA (which he doesn't know so I'm stuck doing it anyway). That's per sheet and there were 2 or 3 sheets. So it wouldn't have been much work - maybe the same as the remaining 20%. Who knows? I recognized that I would use your indirect approach.

    But your approach is definitely tucked away for future usage.

    With my 360 cells, I added about 80K to the file size - not too bad. The 5 circles are 1K or 2K in size, don't know why some are different but don't really care; and don't know why 360x1K only adds 80K but probably some compression when saving.

    But one question on your (new) approach. I couldn't paste link in my macro to the selected one of the 5 pictures. With VBA and using the refersto attribute for the cells where the scores are, do you even need a name for the cells? Could I, in effect, link to the "source" pictures (which I "Inserted Picture" on a sheet called "Grade Pictures") based on the score in the cell? For example, if the score is A, I want the picture called GradeA on Grade Pictures to appear over the A. Paste positions the picture in the upper left of the cell (which he made quite large), hence the need to find the name of what was pasted so I could adjust its position attributes to center it and cover up the letter score. That was a rqmt and probably most of my macro. But that code was free courtesy of the lounge. You're right that the actual "copying" of the picture (be it a clipboard copy or even an insert from a file) is a few trivial lines.

    Fred

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Name that Shape (97+)

    You have name for every picture object whether you give it one or excel does.

    Your 5 "grades" (master pics) have a name (picture 1 - "whatever", I did not bother trying to rename these objects, I let excel do its thing and NOT bother trying the use this name)

    My method has a name for the RANGES that contain the picture. This is a MEANINGFUL name, GradeA, GradeB, GoodJob, BadJob, TickledPink, or whatever. Since it is RANGE of cells, you can have text, multiple pictures, it can even be a formatted DATA TABLE (I use this picture to add XY data to charts), or even a chart object. As long as it is in the defined range it will show up later when we "grab this picture"

    Lastly, and this is what can be come large, EACH "linked" picture has a name. I used A, B, etc, though this could/should also be more meaningful (YTDProdn, CompanyXResults, etc).

    You should be able to link DIRECTLY to the pictures on the file, and possibly on the sheet, though I am not sure how to do this directly WITHOUT using a macro! I wanted a technique purely "formula-driven"

    I used the INDIRECT method, since it made the "links" LIVE to changes in the database.

    Steve

Posting Permissions

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