Trying to reverse arrow head shape in diagram

therealjag

New Member
Joined
Nov 13, 2019
Messages
2
Hi there,

I have a diagram where I've created over a 100 arrows. However I am trying to reverse the arrow heads from flat to triangle and vice versa but my code doesn't seem to be working. The code doesn't recognise the line/connector type so that I can reverse it. any ideas?

Code:
Sub reversearrows()

Dm sh as Shape

For each sh in ActiveSheet.shapes
 if sh.type = msoLine Then
  if sh.line.beginarrowheadstyle = msoarrowheadnone Then
     sh.line.beginarrowheadstyle = msoarrowheadtriangle
     sh.line.endarrowheadstyle = msoarrowheadnone
  elseIf sh.line.beginarrowheadstyle = msoarrowheadTriangle Then
     sh.line.beginarrowheadstyle = msoarrowheadNone
     sh.line.endarrowheadstyle = msoarrowheadTriangle
  end if
end if
Next

End Sub
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,602
Welcome to MrExcel. I think you just need to select each shape:
Code:
Sub reversearrows()

    Dim sh As Shape
    
    For Each sh In ActiveSheet.Shapes
        If sh.Type = msoLine Then
            sh.Select
            If sh.Line.BeginArrowheadStyle = msoArrowheadNone Then
                sh.Line.BeginArrowheadStyle = msoArrowheadTriangle
                sh.Line.EndArrowheadStyle = msoArrowheadNone
            ElseIf sh.Line.BeginArrowheadStyle = msoArrowheadTriangle Then
                sh.Line.BeginArrowheadStyle = msoArrowheadNone
                sh.Line.EndArrowheadStyle = msoArrowheadTriangle
            End If
        End If
    Next
    ActiveCell.Select
    
End Sub
 

therealjag

New Member
Joined
Nov 13, 2019
Messages
2
Hi there,

I managed to figure it out. For some odd reason "sh.type" wasn't recognising the line type property, so I changed it to sh.AutoShapeType which did the trick. Another odd one was that it was returning "-2" for the arrow shape which equates to "msoShapeMixed", again not sure why that is so if anyone has an answer that would be great.

Cheers,

Working code below:

Code:
Sub reversearrows()

    Dim sh As Shape
    
    For Each sh In ActiveSheet.Shapes
     sh.Select
        If sh.AutoShapeType = -2 Then 'msoShapeMixed
            If sh.Line.BeginArrowheadStyle = msoArrowheadNone Then
                sh.Line.BeginArrowheadStyle = msoArrowheadTriangle
                sh.Line.EndArrowheadStyle = msoArrowheadNone
            ElseIf sh.Line.BeginArrowheadStyle = msoArrowheadTriangle Then
                sh.Line.BeginArrowheadStyle = msoArrowheadNone
                sh.Line.EndArrowheadStyle = msoArrowheadTriangle
            End If
        End If
    Next
    ActiveCell.Select
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,162
Messages
5,594,615
Members
413,917
Latest member
devansh02

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top