Paste picture

**swoop99** · 06-24-2011, 06:58 PM

Bit of a noob question this but then.....seeing as I am a noob I suppose it fits.

My workbook (which is unfortunately too large to post) has 2 sheets that are giving me a problem. The first is a search criteria sheet with 4 rows of unlocked cells for users to enter criteria, and a search button (plus some other stuff that's irrelevant to this). The second is where the data from the search is displayed. This part works fine.

However.....a slight oversight on my part means I've only got 2 cells (quite large cells, but only two nonetheless) to play with to display what the actual search criteria was on the display sheet. I'm expecting users to want to print the display sheet, therefore I really need to display the search criteria on it but there's so much data to display I don't have a lot of room left.

Up til now what I've done is have a picture link on the display sheet that shows the range of cells on the 1st sheet, thereby showing the search criteria. And, of course, the picture link both updates automatically and is resized perfectly to fit into these 2 cells I've got spare.

The problem is......that when this picture link is there other code in the spreadsheet seems to run slower that it should, removing the picture link instantly speeds things up. So....I can't use a picture link and there's way too much data displayed on the second sheet to have the search criteria there as well in any other form.

What I'd like to do is instead of a picture link just paste a picture of the cells in question and when a user navigates away from the display page delete it again. The thing is I have no idea how to position a picture created in code or how to delete it again when I don't know it's name.

I've gotten as far as:

Please Login or Register  to view this content.

and after that I'm stuck. How do I position it in code at the same place every time and how do I delete it again?

While I can't post the workbook, I have attached 2 jpegs that show what I mean.

Oh P.S. Just to make it more difficult, this needs to work in all versions of excel from 2002 upwards.

**swoop99** · 06-25-2011, 02:58 PM

Shameless <PUNT>

**Leith Ross** · 06-25-2011, 03:56 PM

Hello swoop99,

This macro will position and size the last added shape on the active worksheet into cells "A1:A2".

Please Login or Register  to view this content.

**swoop99** · 06-25-2011, 04:16 PM

Thank you.

Got more problems now. I came up with this:

Please Login or Register  to view this content.

The SendToBack command is cos I've got another pic on the assessments sheet (called Picture29) which is the same colour as the background and conveniently covers over the 'Search' button on the picture.

And in the macro that navigates away from the Assessments sheet I've put:

Please Login or Register  to view this content.

However, every 2nd time I run this I get the error:

"Run-time error '1004': Unable to get the Paste property of the Pictures class"

It's weird, it'll error and if I click the macro button again it'll work fine.

**swoop99** · 06-25-2011, 04:20 PM

This is freaky. I just tried the following:

Please Login or Register  to view this content.

And it works. So telling the code to try again if it fails......logically that would cause an infinite loop but it doesn't.

Edit. I just tried putting in an error counter, it always comes out as 1. Can anyone tell me why?