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
Andreas HermleTeam leaderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try
Sub macro()

For Each Shp In ActiveSheet.Shapes
    If Shp.Name Like "graphic_*" Then
        Shp.LockAspectRatio = msoTrue
        Shp.ScaleHeight 0.69, msoTrue, msoScaleFromTopLeft
    End If
Next
End Sub

Open in new window

Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fabrice LambertFabrice LambertCommented:
Side notes:

- Don't use objects such as ActiveWorkbook, ActiveSheet, Selection, ActiveSheep as these objects are user dependant, are by nature chaotic and unpredictable.
Prefer referencing explicitly the object you want to use.
If you really have no choice but using ActiveSheet, prefix it with ThisWorkbook.
- Option explicit at top of your modules never hurt.

Also, I would add à parameter, for reusability purpose and respect to SRP.
Sub macro(byref ws As Excel.Worksheet)
    Dim Shp As Excel.Shape

    For Each Shp In ws.Shapes
        If Shp.Name Like "graphic_*" Then
            Shp.LockAspectRatio = msoTrue
            Shp.ScaleHeight 0.69, msoTrue, msoScaleFromTopLeft
        End If
    Next
End Sub

Open in new window

0
Andreas HermleTeam leaderAuthor 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?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Andreas HermleTeam leaderAuthor 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
0
Rgonzo1971Commented:
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

0
Andreas HermleTeam leaderAuthor 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 :-)
0
Fabrice LambertFabrice LambertCommented:
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

0
Andreas HermleTeam leaderAuthor 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.
0
Andreas HermleTeam leaderAuthor Commented:
Thank you very much to both of you. Both codes work just fine. I really appreciate your professional expertise!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.