Hello,
I'm trying to produce a simple excel spreadsheet on my Macbook which automatically changes the image in cell B3 when I change the name in A3.
A3 contains a filename 'pic1' which is saved in a folder on my desktop 'pictures.'
A1 contains =picturelookup(A3,B3,1)
I'm using this code, which I found on the website exceloffthegrid:
I open a blank spreadsheet, enabled developer, right clicked on the sheet1 tab and clicked 'View Code.'
This opened the VBA where I pasted the code see pic above. The file is saved as a macro enabled worksheet xslm file, also on my desktop.
I'm new to VBA and think that I may have missed some very basic steps as the nothing seems to be happening.
Hope you can help,
Dan
Public Function picturelookup(Value As String, Location As Range, Index As Integer)
Application.Volatile
Dim lookupPicture As Shape
Dim sheetName As String
Dim picTop As Double
Dim picLeft As Double
sheetName = Location.Parent.Name
'Delete current picture with the same Index if exists
For Each lookupPicture In Sheets(sheetName).Shapes
If lookupPicture.Name = "PictureLookup" & Index Then
lookupPicture.Delete
End If
Next lookupPicture
'Get position of cell calling the UDF
picTop = Location.Top
picLeft = Location.Left
'Add the picture in the right location
Set lookupPicture = Sheets(sheetName).Shapes.AddPicture _
("/Users/danielcheetham/Desktop/pictures" & Value & ".png", msoFalse, msoTrue, picLeft, picTop, -1, -1)
'change the picture name
lookupPicture.Name = "PictureLookup" & Index
picturelookup = ""
End Function