Adding in JPG image to worksheet, file source location decided via drop down list data validation

[melocoton]

Hello,

Please can you help me to write VBA code that will enable me to do the following.

On Sheet “Front Sheet” when I select a name in the drop down box in cell B1, say “Customer1” I need to go to a location "N:\data\chart\Customer1.JPG" and insert this picture in to range B48:I62 and then go to location "N:\data\graph\Customer1.JPG" and insert this picture in to range R48:Y62. Then when I next select another customer from the drop down box (list data validation) in cell B1 it deletes these two pictures and will repeat/do the same thing if I select Customer2 and Customer3 but with source files "N:\data\chart\Customer2.JPG" & N:\data\graph\Customer2.JPG" and "N:\data\chart\Customer3.JPG" & N:\data\graph\Customer3.JPG"</SPAN>

I have no idea where to start with this or how to pull the code together to work for multiple customers and without layering the pictures on top of one another. Any help you can give me would be greatly appreciated.

Thanks

</SPAN>

 

[texasalynn]

try this

Sub Shapes1()
'Delete all Objects except Comments
    On Error Resume Next
    ActiveSheet.DrawingObjects.Visible = True
    ActiveSheet.DrawingObjects.Delete
    On Error GoTo 0
End Sub
Sub TestInsertPictureInRange()
    Call Shapes1
    PName = Range("B1").Value
    InsertPictureInRange "L:\ALynn\Graphic\" & PName & ".jpg", _
        Range("B48:I62")
    InsertPictureInRange "L:\ALynn\Graphic\" & PName & ".jpg", _
        Range("R48:Y62")
End Sub
Sub InsertPictureInRange(PictureFileName As String, TargetCells As Range)
' inserts a picture and resizes it to fit the TargetCells range
Dim p As Object, t As Double, l As Double, w As Double, h As Double
    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
    If Dir(PictureFileName) = "" Then Exit Sub
    ' import picture
    Set p = ActiveSheet.Pictures.Insert(PictureFileName)
    ' determine positions
    With TargetCells
        t = .Top
        l = .Left
        w = .Offset(0, .Columns.Count).Left - .Left
        h = .Offset(.Rows.Count, 0).Top - .Top
    End With
    ' position picture
    With p
        .Top = t
        .Left = l
        .Width = w
        .Height = h
    End With
    Set p = Nothing
End Sub

you will need to change the paths L:\ALynn\Graphic to yours to match where your files are located

Note: you should make a copy of your file before trying code

 

[melocoton]

Thank you Lynn, really appreciate.

I have added after my previous code, so this is what the following code reads in full. The first part of my code is working but this is still not inserting a picture (no error in VBA, it just leaves the range blank still). Is there something I can check here?

Sub Worksheet_Change(ByVal Target As Range)</SPAN></SPAN>
Customer = Range("B1").Value</SPAN></SPAN>
Application.ScreenUpdating = False</SPAN></SPAN>
Sheets("New Chart Pivots").Select</SPAN></SPAN>
ActiveSheet.PivotTables("PivotTable4").PivotFields("Geography").CurrentPage = Customer</SPAN></SPAN>
ActiveSheet.PivotTables("PivotTable5").PivotFields("Geography").CurrentPage = Customer</SPAN></SPAN>
ActiveSheet.PivotTables("PivotTable7").PivotFields("Geography").CurrentPage = Customer</SPAN></SPAN>
ActiveSheet.PivotTables("PivotTable8").PivotFields("Geography").CurrentPage = Customer</SPAN></SPAN>
ActiveSheet.PivotTables("PivotTable9").PivotFields("Geography").CurrentPage = Customer</SPAN></SPAN>
ActiveSheet.PivotTables("PivotTable10").PivotFields("Geography").CurrentPage = Customer</SPAN></SPAN>
ActiveSheet.PivotTables("PivotTable11").PivotFields("Geography").CurrentPage = Customer</SPAN></SPAN>
ActiveSheet.PivotTables("PivotTable13").PivotFields("Geography").CurrentPage = Customer</SPAN></SPAN>
ActiveSheet.PivotTables("PivotTable15").PivotFields("Geography").CurrentPage = Customer</SPAN></SPAN>
ActiveSheet.PivotTables("PivotTable16").PivotFields("Geography").CurrentPage = Customer</SPAN></SPAN>
ActiveSheet.PivotTables("PivotTable19").PivotFields("Geography").CurrentPage = Customer</SPAN></SPAN>

Sheets("FRONT SHEET").Select</SPAN></SPAN>
End If</SPAN></SPAN>
End Sub</SPAN></SPAN>

Sub Shapes1()</SPAN></SPAN>
'Delete all Objects except Comments</SPAN></SPAN>
On Error Resume Next</SPAN></SPAN>
ActiveSheet.DrawingObjects.Visible = True</SPAN></SPAN>
ActiveSheet.DrawingObjects.Delete</SPAN></SPAN>
On Error GoTo 0</SPAN></SPAN>
End Sub</SPAN></SPAN>
Sub TestInsertPictureInRange()</SPAN></SPAN>
Call Shapes1</SPAN></SPAN>
Customer = Range("B1").Value</SPAN></SPAN></SPAN>
InsertPictureInRange "S:\ filelocation \14.07.2012\" & Customer & ".jpg", _</SPAN></SPAN>
Range("B48:I62")</SPAN></SPAN>
InsertPictureInRange "S:\ filelocation\14.07.2012\" & Customer & ".jpg", _</SPAN></SPAN>
Range("R48:Y62")</SPAN></SPAN>
End Sub</SPAN></SPAN>
Sub InsertPictureInRange(PictureFileName As String, TargetCells As Range)</SPAN></SPAN>
' inserts a picture and resizes it to fit the TargetCells range</SPAN></SPAN>
Dim p As Object, t As Double, l As Double, w As Double, h As Double</SPAN></SPAN>
If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub</SPAN></SPAN>
If Dir(PictureFileName) = "" Then Exit Sub</SPAN></SPAN>
' import picture</SPAN></SPAN>
Set p = ActiveSheet.Pictures.Insert(PictureFileName)</SPAN></SPAN>
' determine positions</SPAN></SPAN>
With TargetCells</SPAN></SPAN>
t = .Top</SPAN></SPAN>
l = .Left</SPAN></SPAN>
w = .Offset(0, .Columns.Count).Left - .Left</SPAN></SPAN>
h = .Offset(.Rows.Count, 0).Top - .Top</SPAN></SPAN>
End With</SPAN></SPAN>
' position picture</SPAN></SPAN>
With p</SPAN></SPAN>
.Top = t</SPAN></SPAN>
.Left = l</SPAN></SPAN>
.Width = w</SPAN></SPAN>
.Height = h</SPAN></SPAN>
End With</SPAN></SPAN>
Set p = Nothing</SPAN></SPAN>
End Sub</SPAN></SPAN>


Thank you in advance.

 

[marka87uk]

You need to call the procedure.

Sheets("FRONT SHEET").Select
</SPAN></SPAN>Shapes1

 

[melocoton]

Hi Mark,</SPAN>

Thanks for your response.

Forgive my ignorance when I speak here, VBA is new to me and I am trying to learn it. Where do I need to call this procedure in the code? I have previously used
Sheets("FRONT SHEET").Select
in the code to return to the main sheet (FRONT SHEET) as the code at the top of the screen moves to another sheet (New Chart Pivots) to update the pivots for some other charts on the front sheet. This is placed at the end of a sub. The code for inserting the jpg. pictures all takes place on the front sheet and this sheet should already be selected from the previous code just mentioned. I am trying to understand why I would directly name Shapes1 after this operation before the new sub. Also is the procedure not already named in this code here:
Sub TestInsertPictureInRange()
Call Shapes1

Can you please explain where I need to name this and what this will be doing in the code so I can understand it and learn?

Thank you so much, apologies if I am misunderstanding all this.</SPAN>

 

[texasalynn]

the placement is what marka87uk was showing you where to place the call in your current code.