Mac VBA & Excel, auto change image based on a cell

Closed
winstoncheets Posts 12 Registration date Thursday June 7, 2018 Status Member Last seen June 9, 2018 - Jun 7, 2018 at 03:21 PM
winstoncheets Posts 12 Registration date Thursday June 7, 2018 Status Member Last seen June 9, 2018 - Jun 9, 2018 at 03:15 PM
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

Related:

7 responses

Blocked Profile
Jun 7, 2018 at 05:23 PM
Are you danielcheetham?
Having some same issue.
Blocked Profile
Jun 8, 2018 at 03:41 PM
@David-Are you using the same code cut and paste?
0
Do this....

Change the path to the absolute path, and not the relative!

Use:
"C:\Users\"

If your username is not "danielcheetham", you need to got to "C:\Users\" and figure out who you are logged in as!

Understand, this is not even close to portable, and don't try it outside of the machine you built it on!



winstoncheets Posts 12 Registration date Thursday June 7, 2018 Status Member Last seen June 9, 2018
Jun 8, 2018 at 01:09 AM
Yes, sorry the path should read "/Users/dc/Desktop/pictures" in both cases, which is the absolute path. Working on a MacBook you don't have a C: drive but I am working on the machine that it was built on.
0
Blocked Profile
Jun 8, 2018 at 10:09 AM
So, lets assume the code is correct at this point, ok? Please show us in your sheet how you are deploying it. Give us an example of the code in usage on the sheet!

Didn't find the answer you are looking for?

Ask a question
It works for me... see example:


In the above example I was not certain if the image would stretch to the RANGE, so I put a large range on it. It seems that the range can be a single cell, so deployment of:

=picturelookup("eagleBadge",E2,1)

will produce the same result as the posted example!
This is all I changed:

("C:\Users\User\Desktop\" & Value & ".png", msoFalse, msoTrue, picLeft, picTop, -1, -1)
--


See example of how to deploy it!


It's kind of fun to do the impossible! -Walter Elias Disney
Here is another example of taking it just one more step, looking up the PNG value in a cell. The below example shows the same code changing the image based on the value in cell A1.



Please remember, this code only looks for PNG files, and if you are using a JPG, it is wrong. You could always just include the file format in the name, and drop the PNG from the static code, if you need the flexibility! See below!



winstoncheets Posts 12 Registration date Thursday June 7, 2018 Status Member Last seen June 9, 2018
Jun 8, 2018 at 03:19 PM
Hi ac3mark,

I created a eagleBadge.png in the pictures folder just so we are working off the same sheet.

I tried deploying it in the View Macros button on the Developer tab and got the error message as below


I tried entering it into the spreadsheet cell A1 and nothing happened,


I'm wandering if it is because Mac OS doesn't have the ActiveX controls..? And if so whether the VBA code could be adapted to use Form Controls instead?

...and thanks for getting involved in the troubleshooting
0
Blocked Profile
Jun 8, 2018 at 03:39 PM
OK, in the VBA developer explorer, add in a new MODULE and place the code into that module, not on the
worksheet. As so...

If you placed the code onto the sheet itself, it needs to be cut from the sheet in the explorer, and added to the new module.

When you begin a new formula, and you start with "=pictur", does the line predictively finish with picturelookup? If not, then it is not in the correct location (module!). The worksheet should see it as a native formula, and try to predict what you want.
winstoncheets Posts 12 Registration date Thursday June 7, 2018 Status Member Last seen June 9, 2018
Jun 8, 2018 at 04:35 PM
Just to clarify, I cut the code from the Sheet 1 and pasted it into the new module as below

In cell A1 I then typed =picturelookup and there was no predictive recognition. Am I typing this in the right place?
0
Blocked Profile
Jun 8, 2018 at 04:53 PM
That looks right. Have you enabled macros? Save the workbook as a MACRO enabled spreadsheet, and open it back up. If defaults to a flat sheet, and you must select save as....xlsm file format.
0
winstoncheets Posts 12 Registration date Thursday June 7, 2018 Status Member Last seen June 9, 2018
Jun 8, 2018 at 05:08 PM
Yep, it is saved as a xlsm file and I've enabled all macros as below

...still nothing
0
winstoncheets Posts 12 Registration date Thursday June 7, 2018 Status Member Last seen June 9, 2018
Jun 8, 2018 at 05:09 PM
any thoughts on whether it may be the ActiveX controls?
0
Blocked Profile
Jun 8, 2018 at 05:19 PM
Ok try this. Make a very simple script. Something like....

function ItFired()
msgbox("Hello World")
end function

And call it....with:
In Cell B1 place: =IF(A1="fire",ItFired())

In Cell A1 place "fire", without quotes!


That way we are trying to determine if it is the firing of the code, or the picture stuff!
0