Can someone check this code?

[farmerscott]

Hi,

I am trying to get a code to draw an arrow/line between 2 cells ("C4" and "F14" in this example).

Using the following code from Draw Line From Center Of Cell To Center Of Another - OzGrid Free Excel/VBA Help Forum

Sub Macro2()
Dim lbx As Long
Dim lby As Long
Dim lex As Long
Dim ley As Long
With Range("C4")
     lbx = .Left + .Width / 2
     lby = .Top + .Height / 2
End With
With Range("F14")
     lex = .Left + .Width / 2
     ley = .Top + .Height / 2
End With
Sheet1.Shapes.AddLine lbx, lby, lex, ley
'expression.AddLine(BeginX, BeginY, EndX, EndY)
End Sub

However, when I run the code, the line does not-

1. come from the middle of cell "C4", it is more from the bottom right of the cell.
2. end at cell "F14" it is in the bottom middle of cell "G16".

I have also tried the following code and it draws the exact same start and end points.

Sub DrawArrows3()

Dim FromRange As Range
Dim ToRange As Range
Dim dleft1 As Double, dleft2 As Double
Dim dtop1 As Double, dtop2 As Double
Dim dheight1 As Double, dheight2 As Double
Dim dwidth1 As Double, dwidth2 As Double

Set FromRange = Range("C4")
Set ToRange = Range("F14")


dleft1 = FromRange.Left
dleft2 = ToRange.Left
dtop1 = FromRange.Top
dtop2 = ToRange.Top
dheight1 = FromRange.Height
dheight2 = ToRange.Height
dwidth1 = FromRange.Width
dwidth2 = ToRange.Width
 
ActiveSheet.Shapes.AddConnector(msoConnectorStraight, dleft1 + dwidth1 / 2, dtop1 + dheight1 / 2, dleft2 + dwidth2 / 2, dtop2 + dheight2 / 2).Select
'format line
With Selection.ShapeRange.Line
    .BeginArrowheadStyle = msoArrowheadNone
    .EndArrowheadStyle = msoArrowheadOpen
    .Weight = 1.5
    .Transparency = 0.5
  
    .BeginArrowheadStyle = msoArrowheadTriangle
    .EndArrowheadStyle = msoArrowheadTriangle
    .ForeColor.RGB = RGB(0, 0, 255)
   
End With
 
 End Sub

Source: Draw Excel Lines or Arrows Between Cells with VBA - wellsr.com

Both codes seem logical in their calculations........
Both codes allow for changes in the width of columns........

What am i missing?

Thanks, FarmerScott

 

[Michael M]

Hi Scott
Used the 2nd macro and...
Works fine for me, regardless of the column width.
You don't have merged cells involved here do you....???

 

[farmerscott]

Hi Michael,

You are onto something. I tried the code on another sheet and it worked fine.

However, it looks like the issue is in the width of the columns. If you change the width, the line does not correspond with the right cells.


Will look at it further...

Hope you have your toes out of the flood water from the Murray.

FS.

 

[Michael M]

I I did change the col widths and it worked fine.....yeah the Murray has a couple of metres to affect us.
You guys ok out there??

 

[farmerscott]

Burrendong is at 120+% capacity. So any heavy falls will cause some big problems.

That equates to around 4 big cotton seasons. Downside is, farmers want to plant now and can't get on the country.

 

[Michael M]

Fingers crossed ol' mate....take care of you and yours