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]

· See below a VBA solution to the problem. I am also posting my test workbook.
· Change a drop down on column A and the corresponding image will update.
· Note the descriptive names for the images on Sheet2.

' sheet module


Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column > 1 Then Exit Sub
Me.Shapes(Target.Offset(, 1).Address).Delete              ' old one
Sheets("sheet2").Shapes(Target & "_" & Me.[c1]).Copy      ' new one
Target.Offset(, 1).Activate                               ' where to paste
Me.Paste
Selection.Name = Target.Offset(, 1).Address               ' shape name is an address
End Sub

Dropbox - Dynamic Image VBA Question TestENS.xlsm