I am unsuccessfully trying to adjust code that was created by a previous coworker. I have very limited VBA experience, so please bare with me. Currently use this code below. It is attached to a button on an excel worksheet, this inserts an image into a specified range of cells, it resizes the image then lands on a cell below to type the description. The problem we are having is our template is now being moved from our server to outside locations. So all of the images are now just broken links. I have attempted several adjustments, but none so far would insert the actual image instead of linking it.
Code:
Private Sub Picture1_Click()
' Select Image From File
With Application.FileDialog(msoFileDialogFilePicker)
If .Show Then
PicLocation = .SelectedItems(1)
Else
PicLocation = ""
End If
End With
' Error Check
If PicLocation = "" Then
MsgBox "No picture selected"
Exit Sub
End If
'Initialization
Dim TargetCells As Range
ActiveSheet.Unprotect
Set TargetCells = Range("B9:H24")
' Error check 2
If PicLocation <> "False" Then
Set p = ActiveSheet.Pictures.Insert(PicLocation)
Else
Exit Sub
End If
' Set image dimensions
With p.ShapeRange
.LockAspectRatio = msoTrue
.Height = TargetCells.Height
If .Width > TargetCells.Width Then .Width = TargetCells.Width
End With
' Set image location
With p
.top = TargetCells.top
.Left = TargetCells.Left
.PrintObject = True
End With
' Close out operations
Range("a25").Select
Set p = Nothing
End Sub