Excel VBA插入箭头宏 [英] Excel vba insert arrow macro

问题描述

我在excel中创建了一个宏，可通过键盘命令插入红色箭头.我通过研究找出了如何使它停止选择在编写宏时选择的单元格的方法，但是我无法弄清楚如何使它每次都在我当前选择的旁边插入箭头.从录制宏时起，它当前将箭头插入到与原始行相同的位置.有办法解决吗?

I created a macro in excel to insert a red arrow via keyboard command. I figured out thru research how to make it stop selecting the cell that was selected when I wrote the macro, but I can't figure out how to make it insert the arrow next to my current selection every time. It currently inserts the arrow in the same spot as my original line from when I recorded the macro. Is there a way around this?

这是代码:

Sub Red_Arrow_Insert()

 Red_Arrow_Insert Macro
 Insert Red Arrow

 Keyboard Shortcut: Ctrl+Shift+A

    ActiveCell.Select
    ActiveSheet.Shapes.AddConnector(msoConnectorStraight, 264, 50.25, 353.25, 139.5 _
        ).Select
        Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadOpen
    With Selection.ShapeRange.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
    End With
    With Selection.ShapeRange.Line
        .Visible = msoTrue
        .Weight = 1.5
    End With
    ActiveCell.Select
End Sub

推荐答案

可以通过使用 ActiveCell 并使用这些数字放置箭头，因为 Top 和 Left 属性是针对文档的左上角测量的，并假定箭头始终是静态长度.

This request can be done by using the Top and Left property of the ActiveCell and using those numbers to place the arrow, since the Top and Left property are measured against the upper left corner of the document and assuming the arrow will always be a static length.

请参见下面的重构代码:

See the refactored code below:

Sub Red_Arrow_Insert()

'Red_Arrow_Insert Macro
'Insert Red Arrow
'Keyboard Shortcut: Ctrl Shift + A

Dim l As Long, t As Long

l = ActiveCell.Left
t = ActiveCell.Top

ActiveSheet.Shapes.AddConnector(msoConnectorStraight, t + 89.25, l + 89.25, l, t).Select

With Selection
    With .ShapeRange.Line
        .EndArrowheadStyle = msoArrowheadOpen
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
        .Weight = 1.5
    End With
End With

ActiveCell.Select 'assumes you want to activate the last active cell.

End Sub

n.b.-我使用89.25作为长度，因为这是原始代码中的点数差异.根据需要进行更改.

这篇关于Excel VBA插入箭头宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！