Thread: changing pictures according to cell value

hi guys

I've a sheet with vba code and many pictures the code's function is to display the picture which i write its name in cell (A1)
it works well
the problem that the code stop to work after adding number of pictures that makes me conclude that excel has limit number of picture or pictures size or there is aproblem
so i hope to find any one who can expaline this form me or solve the problem

Code:

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

i hope to know the reason of the error

Hi,

I tried out your code and it at first didn't work. Then I realised that
I needed to have the formula =A1 in D8. Before doing that the Calculate
Event was not being triggered when pressing Enter after typing a
Picture's name into D8. So now it works well when I type a Picture's
name into A1.

I don't know of any limit to the number of pictures. How many pictures
are you using on the sheet?

Also, you haven't mentioned any error messages, so it's simply stopped
working.

It might be that the Calculate Event is not being triggered. To check
that just add the line...

MsgBox ""

to the code.

If the code is being triggered the message box with an OK button will
appear (provided you didn't place the MsgBox "" line inside a
conditional statement such as If)

Ken Johnson

hi ken ,
thank you for repalying
yes u did right , u can write the word in d8 or make a1=d8
i need to insert more than 1500 pictures ,
the error massage apears after adding 50 pictures nearly
here is the error message :-

Code:

(run-time error  '1004':
unable to set the visible property of the picture class

and in the visual basic window it colores the following sentance with yallow

Code:

Me.Pictures.Visible = True

i am going to the reason is the number of the pictures but may be the reason is different ,so iam waiting to ur suggistions and hope to solve this issue

with my best wishes

Hi frsm,

I've tried, but haven't managed to cause that error.

I can't help feeling the problem is somehow caused by one of your
pictures.

If you are able to email me a copy of the failing workbook I can have a
close look and might be able to find the cause. I too would like to see
the problem solved.

Ken Johnson

hi ken ,
i want to thank you for ur replay
i dnt exact if the reason of the error related to the amount of the pictures or any other reasons .
now u opened anew window that make me examin the pictures type ,maybe there an error in the type of one of the pictures ?!inspite of when i add anew picuter it stop working but if i delete any picture and insert anew one it works well that makes me say thr nuber of the pictures is the reason

dear ken plz send me ur email to send u a file if it is possible

Hi frsm
> dear ken plz send me ur email to send u a file if it is possible

If you can't find it in my profile then...

it's a gmail account so after the @ is...

gmail.com

before the @ is...

kencjohnson

I believe doing it this way reduces the risk of spammers getting access
to yet another email address.

Ken Johnson

Hi frsm,

Have you solved the problem yourself or is there a problem with the
email?

Ken Johnson

hi dear ken
i ddnt solve the problem , but i tried to test the pictures type it took time i make anew file with new pictures with different type and size and the same vba code but there isnt any change
after the 67 pictures it stops working i will send u the file now and try with it and hope u can find the solution

thank you very much for your attention