Macro to associate vlookups on multiple cells with image clicks

I am trying to develop a macro that after click on a picture, will do v-lookups on a bunch of cells in a given row.

For example:

• If I click on a cell in Row 10 and 开发者_高级运维then I click on a picture of a poodle (located somewhere in the sheet, not in Row 10), row 10 will be populated with “animal” in one cell, in the next cell “dog”, and in the next cell “poodle”

• And then I click on a cell in Row 11, and then I click on a picture of a palm tree (located somewhere in the sheet), row 11 will be populated with “plant” in one cell, in the next cell “tree”, and in the next cell “palm tree”

Any ideas? Thanks!

Put some images on your sheet and in the Click event for each one, call the LookupSelection sub with the name of the item contained in the image:

Private Sub imgPalm_Click()
    LookupSelection ("Palm Tree")
End Sub

Private Sub LookupSelection(name As String)
    Dim lookupRange As Range
    Set lookupRange = Sheet1.Range("I3:K5") 'Range for vlookup
    Dim selectedRow As Integer
    selectedRow = Selection.Row
    Dim type1, type2 As String '

    type1 = Application.WorksheetFunction.VLookup(name, lookupRange, 2)
    type2 = Application.WorksheetFunction.VLookup(name, lookupRange, 3)

    Sheet1.Cells(selectedRow, 1) = name
    Sheet1.Cells(selectedRow, 2) = type1
    Sheet1.Cells(selectedRow, 3) = type2
End Sub

You need a lookup table somewhere (I used I3:K5). The finished thing should look something like this:

Good luck!

Create a macro in a regular module to handle the image clicks, and assign it to all of your images' "onaction" property using Right-click >> Assign macro (or in code)

Sub ImageClick()
  Dim rngLookup As Range
  Dim ImgName As String
  ImgName = Application.Caller

  Set rngLookup = ThisWorkbook.Sheets("Lookup").Range("A2:D20")
  With Selection.Cells(1).EntireRow
    .Cells(1).Value = ImgName
    .Cells(2).Value = Application.VLookup(ImgName, rngLookup, 2, False)
    .Cells(3).Value = Application.VLookup(ImgName, rngLookup, 3, False)
    .Cells(4).Value = Application.VLookup(ImgName, rngLookup, 4, False)
  End With
End Sub

As Chris suggested, use a lookup table to fill the values in the selected row: in this case though it's based on the name of the image on the sheet.