We help IT Professionals succeed at work.

FResize graphics on currently active worksheet using VBA

67 Views
Last Modified: 2017-12-04
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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Fabrice LambertConsulting
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Andreas HermleTeam leader

Author

Commented:
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?
Andreas HermleTeam leader

Author

Commented:
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
CERTIFIED EXPERT
Top Expert 2016

Commented:
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

Andreas HermleTeam leader

Author

Commented:
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 LambertConsulting
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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

Andreas HermleTeam leader

Author

Commented:
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.
Andreas HermleTeam leader

Author

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.