Making a dynamic image change depending on which cell is selected

[CoogansBluff]

Is it possible to create a dynamic image that would change by selecting a different cell?

I have dynamic images that change based on a value in a cell, but I want to create one that would change based on the cell that is selected regardless of the value that is in it.

For example, let's say that I've selected cell A1 and want the image to change as I hit inter and move down A2, A3, etc. There may or may not be data entered in these cells.

 

[CoogansBluff]

Just realized that what I want to happen is a little more complicated than I explained.

Let's say I have a batting order of 9 baseball hitters listed in cells A1-A9. The names of those players may change after each game.

In cells B1-B9, I will be entering the results of each player's at-bats, like in a score book.

If I have the cell B1 selected, I want the dynamic image to show the player who is listed in A1 (let's say it's Rickey Henderson).

Then when I hit enter, moving me down to B2, I want the image to show the player who is listed in A2 as batting second (say Joe Morgan).

 

[kevin9999]

With the demo file below, I've assumed your player names and "at-bas" is on sheet 1, and I've put the images of players on a second sheet I called "Players" Change all this as required. It's important that you name your images exactly the same as your players' names in A1:A9. Put the following code in the sheet 1 code module - right-click the sheet tab name, select View Code, and place the code in the window that appears on the right of screen. Save as a macro enabled workbook. Link to the demo file here.

Code here:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Range("B1:B9"), Target) Is Nothing Then
        Application.EnableEvents = False
        Dim img As Object, pick As String, x As String, sh As Shape
        pick = Target.Offset(0, -1).Value
        If Not shapeExists(pick) Then
            MsgBox "Image not found!"
            Application.EnableEvents = True
            Exit Sub
        End If
        Set img = Worksheets("Players").Shapes(pick)    '<-- *** Change name of sheet with the pictures of players
        If Not img Is Nothing Then
            On Error Resume Next
            For Each sh In Me.Shapes
                sh.Delete
            Next sh
            On Error GoTo 0
            img.Copy
            Me.Paste [D1]: Target.Select    '<-- *** Change the location of the image as required
        End If
    End If
    Application.EnableEvents = True
End Sub
Function shapeExists(pick As String) As Boolean
    Dim sh As Shape
    For Each sh In Worksheets("Players").Shapes
         If sh.Name = pick Then shapeExists = True
    Next sh
End Function

 

[CoogansBluff]

Wow, thanks, this is outstanding! I'll study it a bit and might have a follow-up, but you've solved the problem. Thank you!

 

[kevin9999]

Wow, thanks, this is outstanding! I'll study it a bit and might have a follow-up, but you've solved the problem. Thank you!

Happy to help, and thanks for the feedback

 

[CoogansBluff]

Follow-up:

In building this from scratch, how does one set up the dynamic image that is changing? I understand how the photos on the Players sheet works and interacts w/ the code, but might need a refresher on setting up the dynamic image on the main sheet.

 

[kevin9999]

Follow-up:

In building this from scratch, how does one set up the dynamic image that is changing? I understand how the photos on the Players sheet works and interacts w/ the code, but might need a refresher on setting up the dynamic image on the main sheet.

If you mean, how do you position the image on the main sheet, then the following line of code sets the location:

Me.Paste [D1]

Where cell D1 is the top left corner of the image. The existing/current image (and the code assumes there's only one on the main sheet) is removed first via this code:

For Each sh In Me.Shapes
    sh.Delete
Next sh

I hope this is what you were looking for?

 

[CoogansBluff]

I see. That's helpful. Didn't know what the D1 was for. ...

What if I have multiple images on the page? In this example, imagine there are two teams, one using the B1:B9, another using B11:B29. Can it work for two images? And then what if there are other images on the sheet that may not have anything to do with these two?

 

[kevin9999]

One way could be to use the row number in which the image (that you want to delete./replace) sits in (top left cell). So for the demonstration file, the code could look like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Range("B1:B9"), Target) Is Nothing Then
        Application.EnableEvents = False
        Dim img As Object, pick As String, x As String, sh As Shape
        pick = Target.Offset(0, -1).Value
        If Not shapeExists(pick) Then
            MsgBox "Image not found!"
            Application.EnableEvents = True
            Exit Sub
        End If
        Set img = Worksheets("Players").Shapes(pick)    '<-- *** Change name of sheet with the pictures of players
        If Not img Is Nothing Then
            On Error Resume Next
            For Each sh In Me.Shapes
                If Not Application.Intersect(sh.TopLeftCell, Range("1:1")) Is Nothing Then sh.Delete
            Next sh
            On Error GoTo 0
            img.Copy
            Me.Paste [D1]: Target.Select    '<-- *** Change the location of the image as required
        End If
    End If
    Application.EnableEvents = True
End Sub
Function shapeExists(pick As String) As Boolean
    Dim sh As Shape
    For Each sh In Worksheets("Players").Shapes
         If sh.Name = pick Then shapeExists = True
    Next sh
End Function

 

[CoogansBluff]

Thanks again. I can now have multiple images on that sheet with only the one (at D1) that changes.

Hopefully this will be my final question, and I think I've about got it. But how did you make the original image dynamic in the first place?

If I'm trying to replicate what you did, I know how to add images on the Players page and give them names that must correspond with names typed into the A column, etc. What did you do to the image in the main sheet that allows it to change? (btw, I've created dynamic images before, but found them very tricky, and I think I did them in a different way, if that's possible, and that was quite a ways back. Not well-versed in that.)