PDA

View Full Version : Plotting an arrow in excel using VBA



Ray.Mason
05-29-2012, 11:41 AM
Hi Guys!

I am a little stuck in my macro build. I don't know what I'm trying to do is achievable. I need a macro that looks into a cell (e.g A2 and G2). These cells have start and end dates. These dates are then compared to merged cells starting column MN row 6 all the way into calendar which is plotted horizontally. Therefore when start date is matched we plot a line all the way to end date, preferribly double arrow. <-------------------->

Any ideas?

Bob Phillips
05-30-2012, 03:18 AM
This should get you started

Dim arrow As Shape
Dim startCell As Range
Dim endCell As Range

With ActiveSheet

On Error Resume Next
.Shapes("dateline").Delete
On Error GoTo 0

Set startCell = .Rows(6).Find(.Range("A2").Value)
Set endCell = .Rows(6).Find(.Range("G2").Value)
Set arrow = .Shapes.AddConnector(msoConnectorStraight, startCell.Left, startCell.Top - 10, endCell.Left + endCell.Width, startCell.Top - 10)
arrow.Line.BeginArrowheadStyle = msoArrowheadOpen
arrow.Line.EndArrowheadStyle = msoArrowheadOpen
arrow.Name = "dateline"
End With

Ray.Mason
05-30-2012, 04:15 AM
Thanks xld. That will indeed get me started! Will let you know on progress. :)

Many thanks!

Bob Phillips
05-30-2012, 04:25 AM
One small change to span the end of the final merged cell

Dim arrow As Shape
Dim startCell As Range
Dim endCell As Range

With ActiveSheet

On Error Resume Next
.Shapes("dateline").Delete
On Error GoTo 0

Set startCell = .Rows(6).Find(.Range("A2").Value)
Set endCell = .Rows(6).Find(.Range("G2").Value)
Set arrow = .Shapes.AddConnector(msoConnectorStraight, _
startCell.Left, _
startCell.Top - 10, _
endCell.Left + endCell.MergeArea.Width, _
startCell.Top - 10)
arrow.Line.BeginArrowheadStyle = msoArrowheadOpen
arrow.Line.EndArrowheadStyle = msoArrowheadOpen
arrow.Name = "dateline"
End With