Macro Or Command To Change Photo In Excel - Urgent Weekend Help Needed

[garak0410]

We have a critical spreadsheet that we call SHIPPER and it takes Metal Building data from a design program and then eventually converts it into an XML to load into the machines to roll form the material.


The primary sheet has our logo but to save time for the designers, I've created separate sheets for our "private label" customers for when we do a private label job.


The problem I have now is we have so many versions now with 5-6 private labels, that any time I need to make a change the VBA code or conditional formatting, I have to go into each workbook to do it.


So basically, this spreadsheet has our company logo on each of the 14 tabs...and it spans across A-C and 1-10.


I need this Macro to open a file selector prompt, the user choose the proper logo/jpg and it update all 14 tabs with the chosen logo within A-C and 1-10.


And sadly, now I am expected to have it ready Tuesday morning on a 3 day holiday weekend...when this was a backburner projects for many months.


Should this be done with a Macro or is there other more modern ways to do this?

And if someone can help me, I'd be so glad to help you with a "Buy Me a Coffee" link or PayPal as a small token of my appreciation on this 3 day weekend.

[arnelgp]

first thing to do before you apply what is in the demo.
you click on each "Logo" on each sheet (your workbook), and name each image logo as "Logo" (without the quote).
then copy the Module from the demo.
add a button (like in the demo) and add Assign ImagePicker As the Macro.
see the demo now.

[garak0410]

first thing to do before you apply what is in the demo.
you click on each "Logo" on each sheet (your workbook), and name each image logo as "Logo" (without the quote).
then copy the Module from the demo.
add a button (like in the demo) and add Assign ImagePicker As the Macro.
see the demo now.

Thank you so much for the response.

How do I "name" the photo(s)? I see ALT TEXT option but not 100% sure where to do the proper renaming of the photo. I researched about renaming the "shape" but can't find it.


name_01.jpg

[garak0410]

Another update...

It seems to stop at Set shp = sht.Shapes("logo")

name_03.jpg

And shp = nothing

[snb]

Are you famliar with VBA ?
Hire a programmer.

[jolivanes]

"critical spreadsheet that we call SHIPPER" & "primary sheet". Are these the same sheets?
"separate sheets". What are these named? Or are this the 14 tabs? If so, are these tabs the only tabs in the workbook? If not, where are they situated? The last 14? Or maybe the first leftmost 14?
"with 5-6 private labels" Where does this come in with your problem to be solved? Where do we need to watch for this?
"go into each workbook" Which workbooks are that?
"this spreadsheet" Which sheet is that?
"a Macro or is there other more modern ways" How old are your macros if they are compared to "more modern ways"
BTW, what do you consider "more modern ways?


Or is it that you want to select a picture from FileDialogFilePicker, insert it in every sheet in the active workbook and size it to cover A1:C10?

[garak0410]

The code that "arnelgp" kindly provided, I believe is going to work...just having issue with "Shapes" and/or naming the image "logo"...

Option Explicit

Public Sub ImagePicker()
Dim sht As Worksheet, shp As Shape
Dim file As String
Dim fd As FileDialog
Dim t As Single, l As Single, w As Single, h As Single
' get some images
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
    .AllowMultiSelect = False
    With .Filters
        .Clear
        .Add "Images", "*.ani;*.bmp;*.gif;*.ico;*.jpe;*.jpeg;*.jpg;*.pcx;*.png;*.psd;*.tga;*.tif;*.tiff;*.webp;*.wmf"
    End With
    If .Show = -1 Then
        file = .SelectedItems(1)
    End If
End With
'change images on each sheets
If Len(file) <> 0 Then
    For Each sht In ThisWorkbook.Sheets
        sht.Activate
        Set shp = sht.Shapes("logo")
        
        With shp
            t = .Top
            l = .Left
            w = .Width
            h = .Height
            .Delete
        End With
        sht.Pictures.Insert(file).Select
        With Selection
            .Name = "logo"
            .Top = t
            .Left = l
            .Width = w
            .Height = h
        End With
    Next
End If
Sheets(1).Activate
End Sub

[jolivanes]

A quick and dirty possibility.

Sub Maybe()
Dim img As Object, i As Long, a As String
a = ActiveSheet.Name
ChDir "C:\E-Mail Downloads"    '<---- Folder where logos are stored
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .ButtonName = "Submit"
        .Title = "Select an image file"
        .Filters.Clear
        .Filters.Add "JPG", "*.JPG"
        .Filters.Add "JPEG File Interchange Format", "*.JPEG"
        .Filters.Add "Graphics Interchange Format", "*.GIF"
        .Filters.Add "Portable Network Graphics", "*.PNG"
        .Filters.Add "Tag Image File Format", "*.TIFF"
        .Filters.Add "All Pictures", "*.*"
If .Show = -1 Then
Set img = ActiveSheet.Pictures.Insert(.SelectedItems(1))
With img
    .ShapeRange.LockAspectRatio = False
    .Name = "Logo_One"
    .Left = Cells(1, 1).Left
    .Top = Cells(1, 1).Top
    .Width = Cells(1, 4).Left
    .Height = Cells(11, 1).Top
End With
End If
End With
Application.ScreenUpdating = False
ActiveSheet.Shapes("Logo_One").Copy
    For i = 2 To ThisWorkbook.Worksheets.Count
        Sheets(i).Select
        Cells(1, 1).PasteSpecial
    Next i
Sheets(a).Select
Application.ScreenUpdating = True
End Sub

[arnelgp]

logo.jpg

[jolivanes]

A shorter version.

Sub Or_Maybe_So()
Dim x, a As String, i As Long
a = ActiveSheet.Name
    With Application.FileDialog(msoFileDialogFilePicker)
        .Show
        x = .SelectedItems(1)
    End With
ActiveSheet.Shapes.AddPicture(x, False, True, Cells(1, 1).Left, Cells(1, 1).Top, Cells(1, 4).Left, Cells(11, 1).Top).Name = "Logo"
Application.ScreenUpdating = False
ActiveSheet.Shapes("Logo").Copy
    For i = 2 To ThisWorkbook.Worksheets.Count
        With Sheets(i)
            .Select
            .Cells(1, 1).PasteSpecial
        End With
    Next i
Sheets(a).Select
Application.ScreenUpdating = True
End Sub

[snb]

converts it into an XML to load into the machines to roll form the material.

I don't see why the machine needs any logo in an XML (???) file.

@joli:

Sub M_snb()
  Application.Dialogs(342).Show
    
  If Shapes.Count > 0 Then
    With Shapes(1)
      .Name = "logo"
      .Top = 1
      .Left = 1
      .Height = Rows(11).Top
      .Copy
    End With
         
    For Each it In Sheets
      If it.Shapes.Count = 0 Then it.Paste
    Next
  End If
End Sub

[jolivanes]

@snb.
Looks interesting but I have not been able to get it to work (yet).

[garak0410]

"critical spreadsheet that we call SHIPPER" & "primary sheet". Are these the same sheets?
"separate sheets". What are these named? Or are this the 14 tabs? If so, are these tabs the only tabs in the workbook? If not, where are they situated? The last 14? Or maybe the first leftmost 14?
"with 5-6 private labels" Where does this come in with your problem to be solved? Where do we need to watch for this?
"go into each workbook" Which workbooks are that?
"this spreadsheet" Which sheet is that?
"a Macro or is there other more modern ways" How old are your macros if they are compared to "more modern ways"
BTW, what do you consider "more modern ways?


Or is it that you want to select a picture from FileDialogFilePicker, insert it in every sheet in the active workbook and size it to cover A1:C10?

Shipper is the name of the entire project...I should have said on each Worksheet (Not Primary SHeet), there is a logo...there are 14 tabs/worksheets. They have unique names.

The file picker will help the user find the private label JPG.

Again, sorry...only one workbook...should have said worksheet.

Well, not sure if I could use Office Scripting or that new feature that came out but this SHIPPER is so VBA heave, that I might as well keep it.

So, I tried the code ARNELGP provided and it gets stuck on the shape reference.

[garak0410]

Another update...

It seems to stop at Set shp = sht.Shapes("logo")

name_03.jpg

And shp = nothing

Unless it is more involved than this...I think if I resolve this right here, it will work. My deadline is approaching so hope to get it fixed this morning (CDT)...

[georgiboy]

Do you have buttons on each sheet or is the image (logo) the only shape on each tab?

Do you display the image inside of a shape or does the image just sit in the spreadsheet in the specified range?

Have you named each shape where the picture is to reside 'Logo'?

[arnelgp]

it would be much easier if you Upload your excel workbook here.

[garak0410]

Do you have buttons on each sheet or is the image (logo) the only shape on each tab?

Do you display the image inside of a shape or does the image just sit in the spreadsheet in the specified range?

Have you named each shape where the picture is to reside 'Logo'?

Each sheet has this logo at the top left...

logo_01.jpg

There are some buttons on each sheet that are used to check calculations and then send to XML files...

shapes_01.jpg

How can I verify if it is displayed as a shape? I think it is just an image sitting there most likely.

I've gone through and named it logo on each page...

name_04.jpg

But I am thinking since the code he shared is looking for a shape named logo, perhaps I need to we do all of the existing logos as shapes with photos in them and then name them, you think?

[garak0410]

it would be much easier if you Upload your excel workbook here.

I can try...it may not open correctly without the XLA file that it attaches but I am not putting this code in that XLA file so it may open...and there are no trade secrets in this so it is safe...

[arnelgp]

you Upload your Excel file, not some picture if you want this to be resolved the soonest.

[garak0410]

Here's the project without the XLA file, so you may get a few errors on opening...

Shipper.zip