position and resize the same graphics on all worksheets using VBA

Dear Experts:

I got a graphic on every worksheet of my current workbook with the exception of worksheets named 'Results' and 'Overall View'.

All these identical graphics have the same name, i.e. 'my_graphic_1'

I now would like to run a macro that performs the following actions:

Positioning of the graphic 'my_graphic_1' in all of the worksheets (with the exception of worksheets named 'Results' and 'Overall View': From Left = 180 points, From Top = 70 points

Resizing of the graphic 'my_graphic_1' in all of the worksheets (with the exception of worksheets named 'Results' and 'Overall View'): Width = 5 cm, Height = 12 cm, lock Aspect Ratio is off.

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

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

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Hi Andreas,

You may try something like this...

Sub PositionAndResizeGraphics()
Dim ws As Worksheet
Dim shp As Shape

For Each ws In Worksheets
    If ws.Name <> "Results" And ws.Name <> "Overall View" Then
        On Error Resume Next
        Set shp = ws.Shapes("my_graphic_1")
        On Error GoTo 0
        If Not shp Is Nothing Then
            With shp
                .LockAspectRatio = msoFalse
                .Left = 180
                .Top = 70
                .Width = 5 * 28.34646
                .Height = 12 * 28.34646
            End With
        End If
    End If
Next ws
End Sub

Open in new window


Experts Exchange Solution brought to you by ConnectWise

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
Andreas HermleTeam leaderAuthor Commented:
Great Neeraj, thank you very much for it. This did the trick :-)
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You'r welcome Andreas! Glad it worked as desired.
Thanks for the feedback. :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.