How to Programmatically Change Linked Picture Equation

[BrianDP1977]

I pasted a linked picture of a selected group of cells (defined by $GA$2:HE$3) from workbook "TestWorkbook" within tab "Tab1" to a different excel workbook.

The equation defining my linked picture in the new workbook looks like this:

='[TestWorkbook.xlsm]Tab1'!$GA$2:$HE$3

My goal is to create a button that -- when pressed -- will shift what is displayed in the linked workbook to the right one column. In essence, pressing the button would permanently change the linked picture equation to the following:

='[TestWorkbook.xlsm]Tab1'!$GB$2:$HF$3

If I then pressed the button again, it would once more add an iteration to the column values resulting in the following:

='[TestWorkbook.xlsm]Tab1'!$GC$2:$HG$3

Is there any way to program a button that will do this?

[xladept]

Perhaps by just inserting a column all would be copacetic

But you'd have to get the dollar signs out of the formula

[BrianDP1977]

I cannot adjust the original workbook (workbook "TestWorkbook") in any way. All I can adjust is the equation displaying what is shown in the linked picture within the new workbook.

Are you referring to simply adding a COLUMN to the overall button equation by doing something like this within the macro code:

Please Login or Register  to view this content.

The above is definitely not programmed correctly since I'm not sure how to but -- in essence -- what I am trying to do is take the formula defining the selected picture and add a column to said formula. I think that is what you are recommending but am not sure how to correctly do it. Also, since this is just an adjusted recorded macro I have an extra "select" command in it. I assume I can just directly add the column through something like this:

ActiveSheet.Shapes.Range(Array("Picture 10")).Formula = ActiveSheet.Shapes.Range(Array("Picture 10")).Formula + COLUMN

However, this is giving me errors.

[xladept]

Like this:

Please Login or Register  to view this content.

[BrianDP1977]

I get a run-time error 438 "Object doesn't support this property or method" message trying to do it this way. I assume because the object is an image and isn't necessarily referencing the formula defining what is displayed in the image.

I tried adjusting the code to this:

Please Login or Register  to view this content.

However, this gives me a Run-time error 424 "Object required" message.

[xladept]

Maybe:

Please Login or Register  to view this content.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[BrianDP1977]

Attached are workbooks that replicate what I am looking to do (as described in the first post). The "TestWorkbook" contains the information I am referencing for the linked picture within the "ImageWorkbook" workbook. Currently, the button is programmed with the recommendation above and is giving the aforementioned "Object doesn't support this property or method" error message.

* Edited "ImageWorkbook" to include an example of what would happen if you hit the button once. Hitting it a second time would keep shifting everything right by 1 column.

[xladept]

It looks like you want to move the picture and change the link - is that right?

This will move the pic (it's still in progress):

Please Login or Register  to view this content.

[BrianDP1977]

I'm not exactly sure what you mean. Really, all I want to do is somehow just iterate the column portion of the range reference used by the equation defining what is displayed in the image by 1 each time I click the button.

In "ImageWorkbook", the equation defining what is shown in the upper most "linked picture" (an option when you "paste special" within excel) ends with a column range reference of $A$1:$C$3 (data which is located within the "TestWorkbook" workbook). What I want to have happen when I hit the "Shift Right" button is that the column portion of the range reference in the linked picture equation would increment by 1 for both the starting and ending columns (i.e. the equation would change to ending in $B$1:$D$3 as shown in the second example image in the ImageWorkbook example). If I hit the button again, another iteration would occur causing the equation to update to ending in $C$1:$E$3.

[xladept]

I'm trying all kinds of code from help and from "googling" but none has yet worked

[BrianDP1977]

Yep, it's proving tricky. Thank you for trying to work it out though. Hopefully I stumble on a solution at some point.

[xladept]

This works once

Please Login or Register  to view this content.

[xladept]

I think I got it! - Give it a try and let me know

Please Login or Register  to view this content.

[BrianDP1977]

Seems to be working great! Thank you very much!

One quick question, any idea why running this macro causes the position of the pic itself to shift to the right? I assume it is due to this line:

Please Login or Register  to view this content.

I can move it back using the .IncrementLeft command but I'm guessing there is a better way. I'm searching but can't find a way to reference the images position prior to it shifting. If I could, I could just set it's position characteristic back to it's original position after it is shifted running your code.

[xladept]

Yeah, that's the line - Just comment it out i.e. put a single quote in front of it

(and put the "End If" on the next line)

Please Login or Register  to view this content.

This was much more involved than I had originally thought

[BrianDP1977]

Thank you again. With your help I was able to get exactly what I need -- two buttons that now shift what is displayed in the images either left or right -- using the following code:

Please Login or Register  to view this content.

Works perfectly!

[xladept]

Wonderful - nice editing there