Showpic Formula/Macro

jessokane

New Member
Joined
Feb 25, 2016
Messages
11
Hi, I am trying to re-build a workbook with the following criteria. I have since left my old company and I can't remember how the Macro was written and the files it was linked to don't exist on my computer. Please help :) Thanks in advance.

Objective: To use a Macro/Formula to populate a jpeg from a specific file into a specific cell. We are hoping to have a file that we can add the jpeg name/number into and see the image used v having to search/clip/paste each one individually.

Comments:
* The file naming convention for the file path that was used previously was:
File Path\Jpeg Name.jpeg (Note: This is what "AB20" is in the example below)
* Here is an example of the old formula we used to populate an image (in the below case, it went into cell AS42):
=SHOWPIC(AB20,AS42,-20,-40,130,220)
* Here is the Macro written. There were two modules (Note: I have changed some of the letters to xxx so the original file name/path isn't exposed)

Module 1
Function ShowPic(PicFile As String, celDestination As Range, DeltaPointX As Long, DeltaPointY As Long, _
SizePointX As Long, SizePointY As Long) As Boolean
Dim shp As Shape
On Error GoTo Errhandler
With celDestination
For Each shp In .Parent.Shapes
If shp.TopLeftCell.Address = celDestination.Address Then
shp.Delete
ElseIf Round(shp.Left) = Round(.Left + DeltaPointX) And Round(shp.Top) = Round(.Top + DeltaPointY) Then
shp.Delete
End If
Next
Set shp = .Parent.Shapes.AddPicture(PicFile, True, True, .Left + DeltaPointX, .Top + DeltaPointY, _
SizePointX, SizePointY)
End With
ShowPic = True
Exit Function

Errhandler:
ShowPic = False
End Function

Module 2

Sub Showpics()

End Sub
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 08/14/2006 by Alex Gxxxxxg
'
' Keyboard Shortcut: Ctrl+o
'
Range("G3").Select
Selection.Copy
Range("G1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "C:\Documents and settings\gxxxxxxx\desktop\file"
Range("G3").Select
Selection.ShapeRange.Delete
Selection.Copy
Range("G1").Select
Application.CutCopyMode = False
End Sub
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top