Rename Copy Pasted Shape or Disable Shape Copy Paste function

Akanjana

Board Regular
Joined
Mar 22, 2020
Messages
104
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
771
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.
VBA Code:
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

Board Regular
Joined
Mar 22, 2020
Messages
104
Office Version
  1. 2016
Platform
  1. Windows
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.
VBA Code:
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.
 
Last edited:

rlv01

Well-known Member
Joined
May 16, 2017
Messages
771
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

Board Regular
Joined
Mar 22, 2020
Messages
104
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
 
Last edited by a moderator:

Akanjana

Board Regular
Joined
Mar 22, 2020
Messages
104
Office Version
  1. 2016
Platform
  1. Windows
presently i am using workaround
VBA Code:
Private Sub Worksheet_Activate()
Application.OnKey "^c", ""
End sub

To overcome the problem.
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
771

ADVERTISEMENT

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

Board Regular
Joined
Mar 22, 2020
Messages
104
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
May 16, 2017
Messages
771
What is your command button code that inserts the first shape automatically?
 

Akanjana

Board Regular
Joined
Mar 22, 2020
Messages
104
Office Version
  1. 2016
Platform
  1. Windows
What is your command button code that inserts the first shape automatically?

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,653
Messages
5,626,101
Members
416,161
Latest member
David1966Lewis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top