Replacing Logo images across workbook from a specified file location

Hello Everyone!

I have a question that I'm not sure is possible to achieve.

I have a Dashboard Workbook with the following 10 sheets (by name):

• Dashboard
• Measure Definitions
• Data Entry
• Action Plan
• Reporting
• 1
• 2
• 3
• 4
• Update

Every one of these sheets has the same logo on it. It's dimensions (if that is important) is a height of 1.25 inches and a width of 2.14 inches.

Is there a way to set up a macro whereby all the logos can be changed at once to another logo/image?

This dashboard will likely be used by personnel from several different companies/agencies, and I want to see if I can make swapping out the logo easy, rather than them having to manually do it 10 or more times (there are two of the same logo on the Reporting tab, one next to the navigation buttons and one in the print-able area). Even if it can only be achieved with the 10 in the navigation menu, that's more than fine with me.

Is it possible to replace all images without affecting charts or inserted shapes that are on each sheet?

If it is possible, I'd like to arrange it so they can put a file named "logo" in a folder named "PM Dashboard." When you press the associated button, it uses this file location to insert that image in place of all the others. I would put this button on the "Update" worksheet if that is relevant to the VBA code.

If this is not possible, I guess they will simply be out of luck as far as getting the easy way out of logo replacement.

Thanks in advance!

-LM

Hi Ron!!!

First I want to thank you for taking the time to detail this code. I apologize I could not get back to you sooner (other business matters took precedence and I could not even attempt to implement the code until now).

When I ran the code, I did not get any errors (which is good!). But also nothing else happened. The "Done" popup was all that happened after running the macro.

I used your code with the following difference: C:\Users\********\Desktop\PM Dashboard\Logo\LogoPic.jpg The asterisks are not actually in the link, I only blurred out my last name (no spaces).

When I run the macro, the dialog box with "done" appears almost immediately but otherwise nothing else (that I can see) has changed.

Any thoughts on possible corrections (if the whole venture is possible that is)?

I have one final question: I expect the directory of this folder to change. All the dashboard worksheets are located in a folder called PM Dashboard. But this folder as a whole will be distributed and, in some cases, be on a different letter drive. Is there a ambiguous way to refer to the file-path such that it looks at the PM Dashboard Folder and then the Logos sub-folder only?

Thanks so much for your time!

-Brandon

Okay!

So Here is what I've managed to achieve:

1. I learned how to make sure the name of the logo picture in excel matched what your macro called for (that was my initial problem, you had it right).

2. I put a very different picture in the target folder to verify that the logo actually changed. The picture is changing, but its not holding the same dimensions as the original (This is a big goal of mine. If it ends up that the end user has to resize the pics, it would be easier not to use the macro : p).

The dimensions of the pictures in excel now-according to excel and with the exception of one picture that is smaller- are at a height of 1.25 inches and a width of 2.14 inches.

When I run the Macro, the picture that is replaced comes in with dimensions: height of .28 inches and width of .27 inches.

I took off the "Comment" quotes as you suggested but this has not affected the outcome of picture size.

Is there a way to ensure the new logo enters in with the dimensions above?

3. Do you know if the file path can be made flexible? What I mean by this, is that if I zip the folder containing the complete dashboard system, and someone opens it on their computer, will that break this macro's function because its looking for file path referring to my desktop? Is there a way to refer to the folder called "Logo" that is then in in the parent folder "PM Dashboard"?

It's almost where I'd like it and you've been an incredible help! Thanks for taking the time!

-LM

Ron, Wow! You are awesome!

I had another minor issue but managed to fix it myself!

That issue was that every time I ran the macro the image would present properly, but be placed one column to the right.

When I noticed your column offset code I simply changed the 1 to 0 and everything is working smoothly. I used your last reference option and that also works great (I figure I can tell them the folder needs to be on the desktop when they "press the button" in order to update the images. Then it can be moved anywhere)!

I can't thank you enough!

-LM

Hi Ron,

I marked the thread solved but I have one quick follow-up.

Occasionally when the macro is run a few times, the images still move a row or a column in various directions. Is there a way I can specify in the code that I want cell B5 in every sheet to be the top left location the new logo is pasted into?

Thanks,

-LM

I changed the thread back to unsolved just in case members are no longer notified of new responses under the Solved status.

Works like a dream Ron. Fantastic!

Thank you so much,

-LM

I have decided to go with a different solution to this problem but I still would love to understand why the code has broken in this instance.