Rename Copy Pasted Shape or Disable Shape Copy Paste function

[Akanjana]

Hi,
I have a command button which insert flowchart shape in worksheet on click.
once shape is inserted it get unique name automatically. but when user copy paste that inserted shape with ctrl + c and ctrl +v or with right menu click then
shape name is not changed for pasted shape (Shape don't get unique name when manually copy pasted). here i want unique name for pasted shape. or just i can disable shape copy paste keys(ctrl+c and ctrl+v for worksheet) and menu options(copy paste options) itself.

Any workaround ?
Thanks

 

[rlv01]

Every time you paste with CTRL-V, a new shape with a new unique name is created. Why do you think that is not happening? If you run this macro after every CTRL-V paste I think you will see that the newly pasted shape has a unique name.

Sub ListShapes()
    Dim Sh As Shape, S As String

    With ActiveSheet
        If .Shapes.Count > 0 Then
            S = "List of shapes in worksheet " & .Name & ":" & vbCr & vbCr
            For Each Sh In .Shapes
                S = S & Sh.Name & vbCr
            Next Sh
            MsgBox S
        Else
            MsgBox "There are no shapes in this worksheet."
        End If
    End With
End Sub

 

[Akanjana]

Every time you paste with CTRL-V, a new shape with a new unique name is created. Why do you think that is not happening? If you run this macro after every CTRL-V paste I think you will see that the newly pasted shape has a unique name.

Sub ListShapes()
    Dim Sh As Shape, S As String

    With ActiveSheet
        If .Shapes.Count > 0 Then
            S = "List of shapes in worksheet " & .Name & ":" & vbCr & vbCr
            For Each Sh In .Shapes
                S = S & Sh.Name & vbCr
            Next Sh
            MsgBox S
        Else
            MsgBox "There are no shapes in this worksheet."
        End If
    End With
End Sub

Pasted shape is not getting unique name because shape name is changed once after its inserted with command button(insert shape button).

Also to which button or worksheet do i need to assign your above macro code to trigger it after shape paste ?
Thanks for your code.

 

[rlv01]

Pasted shape is not getting unique name because shape name is changed once after its inserted with command button(insert shape button).

When you paste a shape with Ctrl-V, you are creating a new shape. That shape will have a new and unique name. Keep in mind that "name" has a very specific meaning as a property of the shape object. So if you are using the term loosely, say to describe a label or caption and not really the shape's name property, it might seem like the name does not change.

Also to which button or worksheet do i need to assign your above macro code to trigger it after shape paste ?
Thanks for your code.

It is self contained, so just paste it into your workbook's code module someplace. If you want, you can insert a forms button on the sheet that you are doing the Ctrl-V pasting on and assign the ListShapes macro to that button.

 

[Akanjana]

When you paste a shape with Ctrl-V, you are creating a new shape. That shape will have a new and unique name. Keep in mind that "name" has a very specific meaning as a property of the shape object. So if you are using the term loosely, say to describe a label or caption and not really the shape's name property, it might seem like the name does not change.






It is self contained, so just paste it into your workbook's code module someplace. If you want, you can insert a forms button on the sheet that you are doing the Ctrl-V pasting on and assign the ListShapes macro to that button.

@rlv01
1) if we rename a shape after we insert it in worksheet with name box then that shape will not change, if we copy paste it further. its normal behavior in excel. you can please confirm at your end.

2) How to i trigger the macro ? user won't be clicking form button before copy paste shape, even worksheet selection is also not changed on copy paste if at all i want to target macro with selection change.

 

[Akanjana]

presently i am using workaround

Private Sub Worksheet_Activate()
Application.OnKey "^c", ""
End sub

To overcome the problem.

 

[rlv01]

Ok I think I understand what you are saying now, which is that if you manually rename a shape via the worksheet, then when you copy it, the name duplicates. I suppose your onkey method of blocking ctrl-c is as good as any other, though it won't prevent anyone from doing a manual copy with the mouse. Alternatively, you could perhaps add vba code to parse the shapes collection and find and correct any duplicates names; but that depends on your willingness to tackle the coding involved when it sounds like onkey will be a faster practical solution.

 

[Akanjana]

Ok I think I understand what you are saying now, which is that if you manually rename a shape via the worksheet, then when you copy it, the name duplicates. I suppose your onkey method of blocking ctrl-c is as good as any other, though it won't prevent anyone from doing a manual copy with the mouse. Alternatively, you could perhaps add vba code to parse the shapes collection and find and correct any duplicates names; but that depends on your willingness to tackle the coding involved when it sounds like onkey will be a faster practical solution.

yes, even i disabled right menu click copy paste with "worksheet_beforerightclick" event. but still i am waiting for shape renaming part to trigger your above code, apart from these workarounds.

 

[rlv01]

What is your command button code that inserts the first shape automatically?

 

[Akanjana]

What is your command button code that inserts the first shape automatically?

Sub InsertShape(control As IRibbonControl) 'adding flowchart shape

Application.ScreenUpdating = False

    Set Rng = Range("B3")
  
  ActiveSheet.Shapes.AddShape(msoShapeFlowchartProcess, _
  Rng.Left, Rng.Top, 30, 12).Select
   
    With Selection
    .OnAction = "GetSelectedShapeName" ' macro Assigned for further editing shape design
    ' Selection.Name = "TextBox" ' disabled as shape will get unique name on add
    .Characters.Text = "Text1" 

    End With

End sub