I have an Excel workbook which places a picture in cell C5 based on a
drawing number in E56. Although my formula skills are pretty good, my
VBA
isn't. After much Googling the newsgroups I found the following code
(changed to suit my needs):

Sub test()
Dim s As String, i As Integer
s = "S:\Engineering\Drawings\Drawing Generator\Images"
ActiveSheet.Range("C5").Select
With Application.FileSearch
..NewSearch
..LookIn = s
..SearchSubFolders = False
..Filename = "*" & ActiveSheet.Range("E56") & ".EMF"
..MatchTextExactly = True
..Execute
For i = 1 To .FoundFiles.Count
ActiveSheet.DrawingObjects.Delete
ActiveSheet.Pictures.Insert (.FoundFiles(i))
Exit For
Next i
End With
End Sub

This works quite well...except for a couple nagging things that I can't
seem
to get a grasp of.

1. Our logo in B57 disappears everytime the above macro is executed. It

seems as if before the drawing is placed in C5, the screen refreshes.
The
only thing that I can see would be "ActiveSheet.DrawingObjects.Delete".
If
this is correct, how would I go about revising the code? I only want it
to
delete the picture in C5, but keep the logo in B57.

2. The macro is called "sheet10.test", I've made a hotkey (CTRL+d) to
run
the macro but would much rather have a button. Using the "Button"
command in
the Forms Toolbar, I made a button which works fine...once, then it
disappears. I think this is normal for the method I used to create the
button, but is there any way to keep the button on the screen for
additional
use, (i.e. generating a different drawing)?

Correcting the above two problems are of most importance. Soon after, I

would like to rename the macro "Generate Drawing" from "sheet10.test"
just
to keep things clean and simple. I would also like to create an error
message if the drawing does not exist in the folder it is searching in.

However, as mentioned above, items 1 and 2 are where I could use
immediate
assistance, any help would be greatly appreciated.


Many Thanks to a group which has already been alot of help!
Romy