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
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
771
Question: If you are going to such lengths to prevent copying, why not simply protect the worksheet? That would solve the problem without having to disable the copy feature which might be needed for other things?
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

rlv01

Well-known Member
Joined
May 16, 2017
Messages
771
You could also use VBA to prevent any duplicate names for flowchart shapes:
VBA Code:
Sub InsertShape(control As IRibbonControl) 'adding flowchart shape
    Dim Rng As Range

    Application.ScreenUpdating = False

    Set Rng = Range("B3")
    With ActiveSheet.Shapes.AddShape(msoShapeFlowchartProcess, Rng.Left, Rng.Top, 30, 12)

        MakeShapeNamesUnique ActiveSheet 'no duplicate names

        .OnAction = "GetSelectedShapeName" ' macro Assigned for further editing shape design
        .TextFrame.Characters.Text = "Text1"
    End With
    Application.ScreenUpdating = True
End Sub

'Flowcharts only
Sub MakeShapeNamesUnique(WS As Worksheet)
    Dim Sh As Shape, ShapeName As String
    For Each Sh In WS.Shapes
        If Sh.Type = msoAutoShape And Sh.AutoShapeType = msoShapeFlowchartProcess Then
            ShapeName = Sh.Name
            Do While Right(ShapeName, 1) Like "[0-9]"
                ShapeName = Left(ShapeName, Len(ShapeName) - 1)
            Loop
            Sh.Name = ShapeName & Sh.ID               'explicit unique name using shape ID
        End If
    Next Sh
End Sub

A Worksheet event to enforce unique flowchart names:
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MakeShapeNamesUnique Me 'no duplicate names
End Sub
 

Akanjana

Board Regular
Joined
Mar 22, 2020
Messages
104
Office Version
  1. 2016
Platform
  1. Windows
Question: If you are going to such lengths to prevent copying, why not simply protect the worksheet? That would solve the problem without having to disable the copy feature which might be needed for other things?


sorry, i did not inform you about my project. i use these shapes for label design purpose. so i cannot protect object with worksheet protection. So these shapes are need to be available for edit to users and thanks for your last codes for unique naming shapes. i am already working on it. :)
 

Akanjana

Board Regular
Joined
Mar 22, 2020
Messages
104
Office Version
  1. 2016
Platform
  1. Windows
sorry, i did not inform you about my project. i use these shapes for label design purpose (Cell "B3" is label design area. all shapes are inserted here). i cannot protect object with worksheet protection. So these shapes are need to be available for edit to users and thanks for your last codes for unique naming shapes. i am already working on it. :)

Regarding your last code:

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MakeShapeNamesUnique Me 'no duplicate names
End Sub


Their is no selection change in worksheet if user select one another shape. its not possible to trigger event, as all other cells are locked except cell "B3". i need to call macro with .onaction event on shape select with ActiveSheet.Shapes(Application.Caller).Select instead. once shape is selected run your above macro. hope you are getting, the way i will workaround it .
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
771

ADVERTISEMENT

Their is no selection change in worksheet....

Ok, but I think you could find another event that could be used to achieve the same result.
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
771

Akanjana

Board Regular
Joined
Mar 22, 2020
Messages
104
Office Version
  1. 2016
Platform
  1. Windows
I am not suggesting a particular one, because I don't have enough information. Rather, I am suggesting that if you take the time to consider the wide range of available events, you could probably find one that would work for your purposes.

Thanks, i got your point. i will look if any event can helps to achieve the desired result. presently i will assign macro to shape on add. and trigger when shape is selected to change duplicate shape name if any exist. thanks for your support and time. (y) :)
 

Watch MrExcel Video

Forum statistics

Threads
1,128,173
Messages
5,629,147
Members
416,366
Latest member
ChrisDXB1

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