Avatar of Andreas Hermle
Andreas HermleFlag for Germany asked on

FResize graphics on currently active worksheet using VBA

Dear Experts:

for all the graphics on the currently active worksheet whose name start with "graphic_" should be formatted as follows:

Width and Height: 69%
Lock Aspect Ratio: checked
Relative To Original Picture Size: checked.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Andreas Hermle

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Rgonzo1971

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Fabrice Lambert

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Andreas Hermle

Hi Rafael,

thank you very much for your swift help. I just noticed I cannot name graphics on the currently active sheet. I know how to do that for charts, but for graphics, I have got no idea.

Alternatively how would your coding change if I run this code on the selected graphics?
ASKER
Andreas Hermle

Uppps, Rafael I found out myself how to name the graphics (pressing Alt+F10 brings up the dialog box on which to work)

Anyway, how would your code change if I select the graphics and then run the macro?

Hi Fabrice: thank you very much for your help. Will test it and then let you know. I will get back to you tomorrow at the latest.

Thank you very much to both of you. Regards, Andreas
Rgonzo1971

if you want to use the selection

then try
Sub macro1()
For Each Shp In Selection
        Shp.ShapeRange.LockAspectRatio = msoTrue
        Shp.ShapeRange.ScaleHeight 0.69, msoTrue, msoScaleFromTopLeft
Next
End Sub

Open in new window

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
Andreas Hermle

Hi Farbrice,

thank you very much for bringing your points to my attention. As a matter of fact, I am aware of all the things you said, especially that one should never use the 'Selection Object'.

Could you please tell me how to run your macro, if I run it a dialog box pops prompting me to select a macro.

Rafael, both codes work just fine. Thank you very much for :-)
Fabrice Lambert

Well, first you'll need to write a procedure without parameters so it will be callable as macro.
And within the procedure , call the procedure named macro with the right parameter.
Public Sub myProcedure()
    macro ThisWorkbook.Worksheets(1)
End Sub

Open in new window

ASKER
Andreas Hermle

Ok, Fabrice, thank you very much for your help. It works :-)

I suggest distributing the points 700 to 300 since Rafael was quicker to answer.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Andreas Hermle

Thank you very much to both of you. Both codes work just fine. I really appreciate your professional expertise!