VBA Named Range Help?

[Bailey589]

I have a spreadsheet with a few dynamic images and dropdown lists on Sheet1. The lists are all in column A, and the dynamic images(Picture 1- Picture 4) are all in column B (1 per row). The table of imagesthat acts as the basis for the dynamic images is on Sheet2. I have a namedformula:

DynamicImage=INDEX(NamedImageTable,MATCH(INDEX(NamedValuesFromColumnA,ROW()),Sheet2!$A$1:$A$15,1),Sheet1!$C$1)

*in this instance Sheet1$C$1 happens to be a randbetween(2,4) because I have 3columns of images on Sheet2 (column B-D)

I have taken the linked images in Column B on Sheet1 and made them equal toDynamicImage, and it kind of works. HOWEVER, all of the dynamic images displaythe same image. They display the image associated with whatever dropdown list Ihave most recently clicked on in column A. Even if I just click on a cell incolumn A (and don't change the value), all of the images shift to whatever thecorrect corresponding image for that would be.

Is there some VBA code that I can use that will run through these rows 1 at atime, update the dynamic image by figuring out the correct value ofDynamicImage, and then lock that image before moving onto the next one? Oranything that essentially accomplishes the same task?

Currently I have a simple macro on a button that just creates arandbetween(2,4) in Sheet1$C$1, copies and pastes it back into C1, so that itisn't volatile. So if I could make it so every time that button was clicked theimages independently updated and then stayed that way, that would be the endgoal.

NEED SOME VBA HELP PLEASE!!!


EDIT: There might be some way to do it with copying the pictures and then pasting them again, but I'm not exactly sure how that would work and it seems kind of slow.

 

[Worf]

I may be asking the obvious, but one can never be too sure:

· Can you give example addresses for the two following ranges?
o Named image table. Is this on sheet 2?
o Named values from column A. Is this on sheet 1?

· How many images do you have on the worksheet? Are they on sheet 1? Are they all visible? Are they floating directly over the cells or are you using some control?
· When you say three columns of images, do you mean image names or real images?
· Can you give example values of everything that appears on the formula?
· Can you post a link to a test workbook?
· What is the source to populate the dropdown lists? What exactly do they contain?

 

[Bailey589]

Hey Worf, thanks for taking a few minutes to think about this. In answer to your questions:

-Named image table. Is this on sheet 2?-> It is on sheet 2. The actual named range is named "shapes" and refers to: [ =Sheet2!$A$1:INDEX(Sheet2!$D$1:$D$10,COUNTA(Sheet2!$A$1:$A$10)) ]

-Named values from column A. Is this on sheet 1? -> It is on sheet 1. The actual named range is named "dropdowns" and refers to: [ =Sheet1!$A$1:INDEX(Sheet1!$A$1:$A$20,COUNTA(Sheet1!$A$1:$A$20)) ]

-How many images do you have on the worksheet?-> There are 9 on this worksheet in sheet 2 and 14 dynamic images in column B of sheet 1. But ideally this would be able to handle more rows of images on sheet 2 (but never stretching past column D) and more dynamic images on sheet 1.

-Are they on sheet 1?-> The dynamic images are on sheet 1, the static images that the dynamic images pull from are on sheet 2.

-Are they all visible?.-> They are all visible (I don't know how to make them not visible).

-Are they floating directly over the cells or are you using some control?-> They're floating over the cell I think. I just pasted the images into the cells. However, if I expand the size of the rows and columns the pictures move on their own so if that indicates some sort of control that was done automatically by excel.

-When you say three columns of images, do you mean image names or real images?-> Real images. On sheet 2 there are images in rows 1-5 columns B-D. (row 1 is blank in case the user selects the blank dropdown value which corresponds to "" in sheet 2 B1)

-Can you give example values of everything that appears on the formula?-> I don't think I understand the question but using just the three named ranges "shapes", "dropdowns", and DynImg [ =INDEX(shapes,MATCH(INDEX(dropdowns,ROW()),Sheet2!$A$1:$A$15,1),Sheet1!$C$1) ] and the pasted in pictures, the cell doesn't have any other values really.

-Can you post a link to a test workbook?-> I'd be happy to, but I can't figure out how. Do I have to use another site or something? Last time I tried something like this rapidshare existed.

-What is the source to populate the dropdown lists? What exactly do they contain? ->They contain the unique values from Sheet 2 Column A. =Sheet2!A1:A5

 

[Worf]

To provide a link, upload the sample workbook to a sharing site such as Drop Box and paste a link here. Note that, depending on the site, the file must be placed in a public section or some similar name.
Giving me the workbook can save us quite some time…

 

[Bailey589]

Dropbox - Dynamic Image VBA Question Test.xlsm

 

[Bailey589]

Hope this helps, as you can see with the named ranges that all of the dynamic images have the same named range. What I had tried earlier that caused it to be slow was to create a named range, something like DynImg1, DynImg2, etc for each of the dynamic images. It worked, but it got significantly slower.

 

[Worf]

=INDIRECT ("Sheet2!"&ADDRESS (1+MATCH (Sheet3! $G$2, Sheet2! $A$2:$A$5, 0), Sheet3! $C$1))

· The formula above allows choices from a two dimensional image matrix
· Image names are on range a2:a5 at sheet 2
· Images are on range b2:d5 at sheet 2
· Desired image column is on C1 at sheet 3
· Desired image name is on G2 at sheet 3
· Tell me if you need my test workbook
· VBA does not seem necessary with this one.

 

[Bailey589]

Hey Worf,

Really appreciate you taking a look at this. Would you mind posting your test workbook? I have your formula working but what is confusing me is the Sheet 3 G2 value. Also I turned this formula into a named range, but since the "values" that it outputs are formatted as text, I'm not exactly sure how to turn that back into a cell reference that the image will be able to use.

When I started this I was using Indirect to get these dynamic images to work, and they did work, but they were super slow. I'm hoping that I missed something and this works, but I'm concerned that when I get 10-20 of these working with Indirect at the same time things are going to slow down.

 

[Bailey589]

Nevermind about the "I'm not exactly sure how to turn that back into a cell reference that the image will be able to use" thing, I had incorrectly used Indirect when I was trying to put your method in. But I'm still having difficulties implementing this.

 

[Worf]

Apologies. That formula works for a single dynamic image based on a two dimensional array of images and you want a column of dynamic images.
I will think a bit on how to accomplish this, maybe with VBA.