How to disable shape copy paste with vba in worksheet - please check this

[Akanjana]

i have few flowchart shapes in excel sheet. which i use for label designing purpose. but i don't want to allow user to copy paste selected shape object in same worksheet.

Application.copyObjectsWithCells = False

Application.CutCopyMode = False

The reasons for not allowing copy paste to users are as follows:
1) on shape copy paste the name of shape is not getting changed
2) with two shapes with same name, i can not target shape selection with .onaction "macro" assigned to shape.


Excepting following solutions:
1) either to disable shape copy paste and allow shape formatting or
2) rename pasted shape with unique name.
3) any better solution from your end.

Help is highly appreciated. Thank You in advance.

 

[Yongle]

Which shapes should the user not be able to copy ? (for example are they rectangles ?)
Are macros assigned to any of the shapes that you want this to apply to ?

 

[Akanjana]

Which shapes should the user not be able to copy ? (for example are they rectangles ?)
Are macros assigned to any of the shapes that you want this to apply to ?

Hi @Yongle As Mentioned in first post these are flowchart shapes and macro is assigned to each shape with addshape.

 

[Yongle]

one option is to place an invisible active-x object above each shape which prevents the user selecting the shape itself - which stops the user copying the shape.
The 2 objects can be linked together seamlessly and the user would be unaware that there is anything other that the flowchart shape present.
Right-click could be used to allow a copy & paste that can be controlled totally.
Running the assigned macro is not a problem (it can be made to run when user clicks on the active-x object).
Let me know if this method is acceptable and if you need any further guidance.

 

[Akanjana]

one option is to place an invisible active-x object above each shape which prevents the user selecting the shape itself - which stops the user copying the shape.
The 2 objects can be linked together seamlessly and the user would be unaware that there is anything other that the flowchart shape present.
Right-click could be used to allow a copy & paste that can be controlled totally.
Running the assigned macro is not a problem (it can be made to run when user clicks on the active-x object).
Let me know if this method is acceptable and if you need any further guidance.

Will this VBA: Make exact copy of shape, and name it work ?

 

[Akanjana]

one option is to place an invisible active-x object above each shape which prevents the user selecting the shape itself - which stops the user copying the shape.
The 2 objects can be linked together seamlessly and the user would be unaware that there is anything other that the flowchart shape present.
Right-click could be used to allow a copy & paste that can be controlled totally.
Running the assigned macro is not a problem (it can be made to run when user clicks on the active-x object).
Let me know if this method is acceptable and if you need any further guidance.

@Yongle Thanks for your reply. actual purpose of not allowing user to copy paste shape is the shape name not getting changed on paste. but if shape name is getting unique on paste them no issue for my purpose.

I placed a invisible activex textbox on cell "B3" which include all shapes. but shapes is getting copy pasted. but this is not good method even though if it work somehow . can we rename pasted shape automatically with unique name instead ?

 

[Yongle]

how many shapes are there on cell B3 ?

 

[Akanjana]

how many shapes are there on cell B3 ?

More than 10 . item can added more with add button.

 

[Yongle]

To illustrate a couple of techniques
I added 5 shapes to cell B3 and renamed as ShapeA, ShapeB ...ShapeE
ShapeA_Click was assigned to ShapeA (the code for ShapeA_Click is not required for this illustration )
I added an activeX LABEL ( Label1 is the selected object in picture below) and placed it OVER ShapeA ONLY
I added a click event to Label1

When user clicks on ShapeA the user is actually clicking on Label1 which runs the VBA below
The VBA
- COPIES ShapeA
- asks the user to select a cell to PASTE
- asks the user for a NEW SHAPE NAME
- ASSIGNS ShapeA_Click to the new shape

Private Sub Label1_Click()
    Dim cel As Range, shp As Shape, newShp As Shape, newName As String
    Set shp = ActiveSheet.Shapes("ShapeA")
'copy shape
    shp.Copy
'paste to destination cell
    Set cel = Application.InputBox("Select Destination Cell for this shape and click OK", "", , , , , , 8)
    cel.Activate
    ActiveSheet.Pictures.Paste.Select
    Set newShp = ActiveSheet.Shapes(ActiveSheet.Shapes.Count)
    cel.Activate
'assign macro to shape
    newShp.OnAction = "'" & ActiveWorkbook.Name & "'!ShapeA_Click"
'rename the shape
    newName = Application.InputBox("enter NAME of new shape and click OK", "", shp.Name, , , , , 0)
    newShp.Name = newName
End Sub

Using ActiveX object
The main advantage of using the active-X object is that there are several events that can be used unlike a shape which only has a click event
Additionally, which is important for you, it prevents the user from selecting the shape itself

What needs to happen to the pasted shape ?
- I appreciate the above may not do what you want, but it provides a starting point
- if you need further help then I need to understand what happens after the shape has been copied (how it should be named ? does a macro need assigning to it)
- you said the the shapes have a macro assigned - should the macro to run when original shape in B3 is clicked or when the NEW shape is clicked etc

 

[Akanjana]

To illustrate a couple of techniques
I added 5 shapes to cell B3 and renamed as ShapeA, ShapeB ...ShapeE
ShapeA_Click was assigned to ShapeA (the code for ShapeA_Click is not required for this illustration )
I added an activeX LABEL ( Label1 is the selected object in picture below) and placed it OVER ShapeA ONLY
I added a click event to Label1

View attachment 9531

When user clicks on ShapeA the user is actually clicking on Label1 which runs the VBA below
The VBA
- COPIES ShapeA
- asks the user to select a cell to PASTE
- asks the user for a NEW SHAPE NAME
- ASSIGNS ShapeA_Click to the new shape

Private Sub Label1_Click()
    Dim cel As Range, shp As Shape, newShp As Shape, newName As String
    Set shp = ActiveSheet.Shapes("ShapeA")
'copy shape
    shp.Copy
'paste to destination cell
    Set cel = Application.InputBox("Select Destination Cell for this shape and click OK", "", , , , , , 8)
    cel.Activate
    ActiveSheet.Pictures.Paste.Select
    Set newShp = ActiveSheet.Shapes(ActiveSheet.Shapes.Count)
    cel.Activate
'assign macro to shape
    newShp.OnAction = "'" & ActiveWorkbook.Name & "'!ShapeA_Click"
'rename the shape
    newName = Application.InputBox("enter NAME of new shape and click OK", "", shp.Name, , , , , 0)
    newShp.Name = newName
End Sub

Using ActiveX object
The main advantage of using the active-X object is that there are several events that can be used unlike a shape which only has a click event
Additionally, which is important for you, it prevents the user from selecting the shape itself

What needs to happen to the pasted shape ?
- I appreciate the above may not do what you want, but it provides a starting point
- if you need further help then I need to understand what happens after the shape has been copied (how it should be named ? does a macro need assigning to it)
- you said the the shapes have a macro assigned - should the macro to run when original shape in B3 is clicked or when the NEW shape is clicked etc

In your code above i can't find "ShapeA_Click" marco codes and details. anyway.
Normally when user click on shape and it get selected and when user hit ctrl + c and ctrl + v it get pasted with same name in excel 2016. so pasted shape need to automatically have a new name assigned to it without further any popup or inputbox asking for shape name. After paste the onaction macro also get copy pasted to duplicate shape which was assigned to Original shape. hope it makes clear. thanks