insert picture

[damorrison]

One of my workbooks requires the user to insert a picture into a
worksheet by pressing the insert picture button and finding the
directory the pictures are in and selecting and inserting it.
How can I make sure the directory is available every time the insert
picture is pressed, because right know when ever the workbook is opened
and the insert is selected it always goes to mypictures directory
instead of the directory that we want.
dave

 

[Dave Peterson]

Maybe you could give the user a macro to insert the picture. Then you'll have
more control over what happens:

Option Explicit
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
Sub ChDirNet(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub
Sub testme01()

Dim myPictureName As Variant
Dim myPict As Picture
Dim myRng As Range
Dim myCurFolder As String
Dim myNewFolder As String

myCurFolder = CurDir
myNewFolder = "yourfoldernamehere"

On Error Resume Next
ChDirNet myNewFolder
If Err.Number <> 0 Then
'what should happen
MsgBox "Please change to your own folder"
Err.Clear
End If
On Error GoTo 0

myPictureName = Application.GetOpenFilename _
(filefilter:="Picture Files,*.jpg;*.bmp;*.tif;*.gif")

ChDirNet myCurFolder

If myPictureName = False Then
Exit Sub 'user hit cancel
End If

Set myRng = ActiveCell
Set myPict = myRng.Parent.Pictures.Insert(myPictureName)
myPict.Top = myRng.Top
myPict.Width = myRng.Width
myPict.Height = myRng.Height
myPict.Left = myRng.Left
myPict.Placement = xlMoveAndSize

End Sub