Changing all contents of another tab with a click

[ricop23]

I am working with a colleague who would like me to create a workbook that reacts to a click in a cell by changing the contents of the other tabs in a workbook and am a bit baffled by his ask.

For example, if the "selections" were various fruits, the other tab with "data" might be a picture of the chosen fruit. This colleague says that in the past, someone set up a workbook for him such that he could select an item on the selector tab and populate the data tab(s) with data related to that item. And do this with each item on the list, automatically storing the associated data to be viewed with a click on the selector tab.

See attached crude example...he thinks that you can create a macro or something that will change all the contents of the same data tab (the picture here, once loaded in) without any other effort from the user than clicking the fruit in the "selector" list.

Does anyone know of a way to do something like this? Simpler, although a bit labor-intensive for a long list, solution to me would be to create corresponding data tabs for all the list's items and hyperlink to the those. You could then write a macro to hide all but the selection's data tabs, so on a click from the list you're left with the selector tab and the data tab(s) of interest visible, but others hidden. Curious if the original ask is actual Excel functionality, though, since it would be sleeker...

 

[JLGWhiz]

You are looking at a major project for a novice.

 

[ricop23]

I wouldn’t consider myself a novice given that profession revolves around excel modeling, but certainly haven’t come across a project like this.

 

[mikerickson]

If you have a picture of an apple and a picture of a banana on sheet Picture.
Putting code like this in the code module for the master sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim onePic As Shape
    With Target
        If .Cells.Count > 1 Then
            For Each onePic In Sheets("Pictures").Shapes
                onePic.Visible = msoFalse
            Next onePic
        End If
        On Error Resume Next
            Sheets("Pictures").Shapes(Target.Value).Visible = msoTrue
        On Error GoTo 0
    End With
End Sub

Although I would put it in the Double Click event.

 

[JLGWhiz]

@mikerickson - That would get the pictures up, but the OP also mentions populating cells with details for each items, so that data has to be stored somewhere and called for each different item. The code would probably be pretty simple. It is building the data base that the data will be retrieved from that could be a real pain. But the OP does not elaborate on what the data is to be populated, so I could be overstating the complexity as I perceive it.

 

[mikerickson]

Depending on how much static data there is for each item, the text could be stored in another shape. Pehaps grouped with the image.