I'm having an odd issue with excel 2007 and Im hoping someone knows what is going on and how to fix it. The code below basically copies in a pdf and embeds it. The code works fine. The user then loads another file and the code below reruns deleting and replacing the embedded pdf. (again works fine) the problem is each time you do this if you do anything that causes the screen to refresh it cycles through all previous pdfs. So if you loaded 100 files cycles through the previous 99 before loading the 100th. Doesnt do this in 2010 but it does keep storing data in the clipboards of 2007 and 2010 as well. If you exit excel and re-open it clears the issue and it starts over. How do I program this correctly to avoid the issue? Is there a way to clear the memory?
Also Application.CutCopyMode = False doesnt work either.
Thanks
Also Application.CutCopyMode = False doesnt work either.
Thanks
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("A1")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
Dim wsh As Worksheet
Dim ole As OLEObject
For Each wsh In ActiveWorkbook.Worksheets
For Each ole In wsh.OLEObjects
Debug.Print ole.progID & ": " & ole.Name
If Left(ole.progID, 8) = "AcroExch" Then ole.Delete
Next ole
Next wsh
ActiveCell.Offset(0, 0).Range("j1").Select
ActiveSheet.OLEObjects.Add(Filename:=Range("a1").Value, Link:= _
False, DisplayAsIcon:=False).Select
Sheets("reporting").Select
Sheets("reporting").Range("a1").Select
ActiveCell.Offset(0, 0).Range("b13").Select
ActiveSheet.OLEObjects.Add(Filename:=Sheets("report tool").Range("A1").Value, Link:= _
False, DisplayAsIcon:=False).Select
For Each wsh In ActiveWorkbook.Worksheets
For Each ole In wsh.OLEObjects
Debug.Print ole.progID & ": " & ole.Name
If Left(ole.progID, 8) = "AcroExch" Then ole.SendToBack
Next ole
Next wsh
End If
End Sub