Paste a picture in Excel and keep it in that location

[Jadegirl]

Good afternoon,

Does anyone know if there is a method to paste a picture / image in Excel and keep it in the region it is pasted in if more cells are added, deleted, sorted, or filtered?

thanks

 

[repairman615]

Hello,

Try right-clicking the picture, then select size and properties.
Then choose the properties tab and tick the "Don't move or size with cells"

-Jeff

 

[Jadegirl]

Thanks that kinda worked, but when I filter all of the pictures roll up to the top even if I don't have those pictures in the area I would like to filter. I wonder if there is anyway to assign them to a cell so they roll up w/ a filter to be displayed or hidden depending on what is being filtered.

Thanks

 

[repairman615]

Hello,

I am not quite sure exactly what you are trying to accomplish. Would you explain a bit further.

Here is what I am working on:

I am currently experimenting with some VBA code to show or hide pictures. I have a pic for each entry in a table list. Depending on the the results of the filter, the corresponding pic will show/hide.

The pics I am using have been renamed like A2 pic, A3 pic ... this gives a reference to the row so the macro can identify which to show/hide.

I am having a little trouble with trapping the filter event (which doesn't exist). One solution is using the calculate event. However, this does not seem to 'fire' after a filter is changed for me. Another solution is using the after calculate event (2007) which shows some promise... I will keep working on it.

I am using the move but don't size property to allow the pics to move up and down depending on the hidden rows.

About how many pics?
What version of Excel? I am using 2007.
What is the table range?
Is renaming the pictures OK?

Here is what I have so far (work in progress):

standard module

Dim X As New Class1
Sub InitializeApp()
 Set X.App = Application
End Sub

class module

Public WithEvents App As Application
 
Private Sub App_AfterCalculate()
 MsgBox "After Calc"
 
Dim cell As Range
Dim rngTable As Range
on error resume next
Set rngTable = Range("D2:D4") '''Table range (without header)
    For Each cell In rngTable
        If cell.EntireRow.Hidden <> True Then
            ActiveSheet.Shapes("A" & cell.Row & " pic").Visible = True
        Else
            ActiveSheet.Shapes("A" & cell.Row & " pic").Visible = False
        End If
    Next cell
on error goto 0
End Sub

If you would like to test the above, on a copy:
Alt+F11 to open VBEditor
Keystrokes Alt - I - C to insert a class module.
Paste the above code labeled class module.
Edit the table range to one column of your table

Keystrokes Alt - I - M to insert a standard module.
Paste the above code labeled standard module.
Place your cursor somewhere in the middle of the code.
Press F5

Now close the VBEditor. Each picture will need to be renamed as described above... A4 pic, A500 pic, etc. Lastly, and hopefully, change the filter to see if it works.


-Jeff

 

[Jadegirl]

Thank so for your reply.

I have a worksheet - actually Excel formulas and how to use them w/ filters so that I can roll up and see everything pertaining to one type of formula together - Example Date, Sum product, Sum if, VLookup, Cond. formatting, etc.. . Sometimes I paste in pictures from the internet which explain the formula quite well. So now when I filter up, all my pictures collide. What I'd like to do is assign the top left corner of the picture to a cell. Then if I chouse a filter only the pictures attached to the cell for the specified filter will be visible. The other pictures will hide just like the cells in the filter.

Ex:
<table width="327" border="0" cellpadding="0" cellspacing="0"><col width="99"><col width="23"><col width="141"><col width="64"><tr height="46"> <td class="xl67" style="height: 34.5pt; width: 74pt;" width="99" height="46"><table width="455" border="0" cellpadding="0" cellspacing="0"><col width="99"><col width="23"><col width="141"><col width="64" span="3"><tr height="46"> <td class="xl67" style="height: 34.5pt; width: 74pt;" width="99" height="46">Main category</td> <td class="xl71" style="width: 17pt;" width="23">Order of category- detailed</td> <td class="xl68" style="width: 106pt;" width="141">Detailed Category</td> <td class="xl67" style="width: 48pt;" width="64">Formula</td> <td class="xl67" style="width: 48pt;" width="64">Practice
</td> <td class="xl67" style="width: 48pt;" width="64">Picture</td> </tr></table></td> <td class="xl71" style="width: 17pt;" width="23">
</td> <td class="xl68" style="width: 106pt;" width="141">
</td> <td class="xl67" style="width: 48pt;" width="64">
</td> </tr></table>
<table width="699" border="0" cellpadding="0" cellspacing="0"><col width="99"><col width="23"><col width="141"><col width="64" span="3"><col width="77"><col width="58"><col width="109"><tr height="45"> <td class="xl65" style="height: 33.75pt; width: 74pt;" width="99" height="45">Date</td> <td class="xl65" style="width: 17pt;" width="23">
</td> <td class="xl67" style="width: 106pt;" width="141"> Date</td> <td colspan="6" class="xl68" style="width: 328pt;" width="436">IF(C4>TODAY()-30,IF(SUMPRODUCT(--($B$4:$B$53=B4),--($C$4:$C$53>(TODAY()-30)))>1,"R","N"),"N")</td> </tr></table>
Thus, I will want to see the pictures only for this filter of the main category Date by the cell assigned to cell: K 88, but it will roll up all the pictures even if my filter isn't in the main category - Date.

Funny - in my efforts to learn excel... I have problems getting there.

I haven't started w/ the VBA coding & Macros yet... I think I can get there one day, but I am still hesitant at this point to start using it.

I am in MS 2007 and so far I have formulas listed to cell 102 and about 10 pics. I;d like to use more if i can learn how to attach them to a cell.

Any tips?
thanks!

 

[repairman615]

Hello,

The code would do that to a degree...

I do have an idea for you.

I think you may like the "comment picture".

Try this out.

right click the cell >>
insert comment >>
now right click the border of the comment >>
format comment >>
choose colors and lines tab >>
select fill > color: drop-down near top >>
choose fill effects... >>
picture tab and then browse for your picture and press OK.

Now your picture is in the comment of the cell and linked to that specific cell.

It will show whenever the mouse hoovers over that cell.

To always show, You may right click the cell again and choose show/hide which I think would be better for you.
Filtered hidden cells will also hide the comment for you.

Does that help?

-Jeff

 

[Jadegirl]

What a perfect solution. Thanks it works beautifully!!!