Excel Picture Database

I need some help, can't find anything and tried everything. I’m no expert in excel or macro. Let me start.

I have excel worksheet with 2 sheets in it. Let’s call the one "Database", it consist of 713 rows and 15 columns with data in of my product. Then the other one "Display Product" in it you type in the code of the specific product so it search for my code and display all of the information of that product code on the "Display Product" sheet and it that work.

Now I want to link a picture to that specific product to the code. Here's the code I used:

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("A71")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With

End Sub

Now this code works fine up to a specific limit in my case 66 picture to a product, and I need at least 200 more images in it but I get a runtime error in my macro when I put one more image in. This is where I get the run time error -> “Me.Pictures.Visible = False“.
I don't know if excel is limited to image usage. Or maybe there is another way to do what I want to do with more advance coding. Can anyone help?

Hello Sonskyn,

Your post is quite muddled. I suspect your problems stem from 2 factors. Firstly, your macro is in the Worksheet_Calculate() event, not certain as to why you placed it here. Every time a cell with a Formula changes, or is called by another formula the macro is run. This will slow your program to a crawl.

Secondly, Storing the pictures on a worksheet takes up a large amount memory. Unless you have issues with program distribution, it is a better to save them in a separate folder, and display the picture when needed. I don't advise using Hyperlinks as there many problematic issues that can arise when using them with VBA coding.

Sincerely,
Leith Ross

Originally Posted by JR@SGC

Hi,

Are you following this info?

HTML Code: 

If so are you using the lookup?
As there should be no limit

Thanks for the reply. Yes I followed that info. And bad thing is I reach a limit looks like it.

This is the error I get: Run-time error '1004': Unable to set the Visible property of the Picture class..

Originally Posted by Leith Ross

Hello Sonskyn,

Your post is quite muddled. I suspect your problems stem from 2 factors. Firstly, your macro is in the Worksheet_Calculate() event, not certain as to why you placed it here. Every time a cell with a Formula changes, or is called by another formula the macro is run. This will slow your program to a crawl.

Secondly, Storing the pictures on a worksheet takes up a large amount memory. Unless you have issues with program distribution, it is a better to save them in a separate folder, and display the picture when needed. I don't advise using Hyperlinks as there many problematic issues that can arise when using them with VBA coding.

Sincerely,
Leith Ross

Hi Ross. Sorry that the post is muddled you can get info on the similiar worksheet im working on at:
http://www.mcgimpsey.com/excel/lookuppics.html their you can download an example worksheet at the bottom of the tutorial. Thanks to JR for this link. My worksheet is just using alot more data than that one.

Originally Posted by JR@SGC

Also

have you got this at the top of your module?

Please Login or Register  to view this content.

Could you post your workbook? If so could you remove all confidential data.

What is this Option Explicit. Sorry I have the ask boss just expect me to to this in excel and I know nothing about excel. Won't be able to post the worksheet.

Hi JR here the link where u can get my worksheet www.brandunlimited.co.za/picture database.xls . If you look in the Database sheet you will see that I highlighted the row where it stop working.

Originally Posted by JR@SGC

There is apparently a limit to how many pictures you can do visible= false to.
The only work around i can think off is to have all your pictures the same size and stacked on top of each other so you can only see the top one.

this code (modified from above) will aways show the selected on at the top.

Please Login or Register  to view this content.

hope it helps

Thanks for all the help JR. I will see what I can do. So far it looks like its doing sumthing.