placing picture when certain criteria is met

[Foxy Lady]

Hi All,

I have used the lookup function to place pictures when certain criteria is met, however I have used to many pictures and some I have renamed wrongly, thus making my spreadsheet very slow. I can't seem to locate the pictures to edit them.

Is there any way that I can use vba to place pictures.

On Sheet1 I have the stick figures and on sheet 2 I have the pivot tabel.
\
=VLOOKUP(B16, PicTable, 4, FALSE)

Also I need an userform to add new data to the pivot table and then adjust the vba accordingly.

Thanx in advance

Please tell my how do I attach the sheet to here

 

[Sandeep Warrier]

Hi Foxy Lady,

Have a look at

1. http://www.mcgimpsey.com/excel/lookuppics.html

2. http://forums.techguy.org/business-applications/723855-solved-insert-image-based-cell.html


For the 2nd part, you would need to specify the type/format of data and also specify the code you're currently using.

I don't think you can attach a workbook here, but you could upload it on a shared site and paste the link here so that others could download it.
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[Foxy Lady]

Hi Sandeep,

I am currently using the mcgimpsey example, however it makes my sheet to be very slow and I can see all the pictures that I have imported to take out those that I mispelled. Isn't there VBA code to do this and wouldn't it make my spreadsheet faster?


The second part, I just need to establish a userform for the user to enter a new picture and the discription for it and then it need to be added to the list.

Hope to hear from you soon.

 

[Sandeep Warrier]

Hi Foxy Lady,

Hi Sandeep,

I am currently using the mcgimpsey example, however it makes my sheet to be very slow and I can see all the pictures that I have imported to take out those that I mispelled. Isn't there VBA code to do this and wouldn't it make my spreadsheet faster?

I think that if you have a lot of pictures in your workbook, it will be slow no matter what method you use.

Try looking at

1. Clip Art Selection in http://www.contextures.com/excelfiles.html#DataVal

2. Named Range Pictures in http://www.contextures.com/excelfiles.html#Function

3. http://www.mrexcel.com/archive/General/12475.html

I'm not sure if any of these will help in improving the speed. Quite a few forums say that the McGimpsey method is the best.

The second part, I just need to establish a userform for the user to enter a new picture and the discription for it and then it need to be added to the list.

The 2nd link that I had given in my last post does give a few examples for attaching pictures.

Also, have a look at http://www.exceltip.com/st/Insert_pictures_using_VBA_in_Microsoft_Excel/486.html

You could modify the "TestInsertPicture" Sub from the code to a Command Button Click event code for your userform so that the main code will run when you click the button.

You could add another parameter for the name

Sub InsertPicture(PictureFileName As String, TargetCell As Range, CenterH As Boolean, CenterV As Boolean[COLOR=Red], PictureName as String[/COLOR])

Also change

    With p
        .Top = t
        .Left = l
    End With

to

    With p
        .Top = t
        .Left = l
        .Name = PictureName
    End With

And when you call the function in your button click event, you could have something like

Private Sub CommandButton1_Click()
    InsertPicture Userform1.Textbox1.Text, _
        Range("D10"), True, True, Userform1.Textbox2.Text
End Sub

I hope this helps.... I've never used pictures with VBA so I'm not very sure...
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden"><input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden"><input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">