VBA Code - Paste shape in selected cell

[Spaztic]

Hello, I'm trying to figure out how to adjust my code so that when I:

• Select whatever cell I want (D6 in the example below)
• Click on the 'arrow image which runs the macro'
• The arrow gets formatted and put in the selected cell in the first bullet (D6 in the example below)

My code is putting the arrow somewhere else (second image).

Any idea how to do this?

Sub Arrow()
' Arrow Macro
    ActiveSheet.Shapes.Range(Array("Straight Arrow Connector 7")).Select
    Selection.Copy
    ActiveSheet.Paste
    
    With Selection.ShapeRange.Line
        .ForeColor.RGB = RGB(192, 0, 0)
        .Weight = 2.5
    End With
    With Selection.ShapeRange.Glow
        .Color.ObjectThemeColor = msoThemeColorBackground1
        .Radius = 3
    End With
End Sub

 

[rlv01]

The .Left and .Top properties are used to position the shape or shape range. It is better to take Select out of the mix. Also better to work with the Shape instead of the ShapeRange (which is what the macro recorder mostly uses). See if this works fpr you.

Sub Arrow()
    Dim Sh As Shape
   
    With ActiveSheet
        .Shapes("Straight Arrow Connector 7").Copy
        .Paste 'inital position at selected cell
        Set Sh = .Shapes(.Shapes.count) 'the newest shape
    End With
      
    With Sh
        'Set position
        .Top = ActiveCell.Top
        .Left = ActiveCell.Left
       
        'fine tune position
        .IncrementLeft -3.5
        .IncrementTop 4
       
        With .Line
            .ForeColor.RGB = RGB(192, 0, 0)
            .Weight = 2.5
        End With
       
        With .Glow
            .Color.ObjectThemeColor = msoThemeColorBackground1
            .Radius = 3
        End With
    End With
End Sub

 

[Spaztic]

This works very well with one issue.

• After it makes the new arrow, I click anywhere but that new arrow
• Then when I hover over that new arrow with the cursor... it treats the new arrow just like the original arrow I assigned the macro to (the new arrow is also used as a macro).

Not sure why...

 

[rlv01]

Then when I hover over that new arrow with the cursor... it treats the new arrow just like the original arrow

I'm not sure what that means. Your original code, and the code I posted copies the (arrow) shape named "Straight Arrow Connector 7" to a new shape with a slightly different name (probably "Straight Arrow Connector n", where in is the item number of the last shape created). It sets a few properties and that's it. The new arrow is a copy of the original arrow so it stands to reason that they would be the same.

 

[Spaztic]

Sorry for not explaining more accurately.
The white looking arrow is assigned a macro. That macro takes that same arrow you click, reformat, and pastes it into the selected cell. However, it seems that the new arrow that is created is also assigned that very same macro. So if I click on the new arrow, it creates another new arrow.

I was wondering if there was a way to 'turn off' the assigned macro for the newly created arrow. If not, I may have do something different where the item being copied wasn't already assigned a macro.

 

[rlv01]

assigned that very same macro. So if I click on the new arrow, it creates another new arrow.

Yes because assigning a macro is done by assigning a value to the shape's OnAction property. When you copy a shape, you copy that property, so it is going to run the same macro.

I was wondering if there was a way to 'turn off' the assigned macro for the newly created arrow. If not, I may have do something different where the item being copied wasn't already assigned a macro.

If you want it to run a different macro or not run a macro, you have to set or clear the new shapes' .OnAction property in your Arrow macro.

Shape.OnAction property (Excel)

Office VBA reference topic

learn.microsoft.com