How to make a locked shape be draggable but restrict its visual formatting?

[Alex Martial]

Hello Colleagues!

I have a little problem with making a locked shape be draggable/moveable by a user.

What am I trying to achieve?

1. There is a task for a user in an Excel file where they need to drag-n-drop several shapes (the blue ones on the attached screenshot) to appropriate positions (the grey ones on the attached screenshot) and in the appropriate order.

2. The worksheet was protected and idiot-user-proofed.

3. I need to allow a user to drag-n-drop a shape on the protected worksheet but I need to restrict any visual formatting, text editing and/or accidental deleting of a shape.

4. If I lock a shape it cannot be moved/drag-n-dropped. If I lock a shape but allow Object Editing, it can be moved but it is not protected, i.e. it can also be changed/edited/deleted. If I do not lock a shape, it can be moved, but it also can be changed/edited/deleted.

5. I found that there is the following approach: add all such shapes into ChartArea of a blank ChartObject and apply:

.ProtectFormatting = True 
.ProtectSelection = False

to the ChartObject. This solves a problem only partially - this is a good way to block a visual formatting, however, you cannot move the shapes. Actually, you can move them, but they will be pushed back to their initial positions, once you drop a mouse button.
If you apply

.ProtectSelection = True

, you can interact neither with ChartArea, nor with the linked shapes.

6. I tried another approach, locked all shapes, created a transparent blank label and wrote a listener for MouseDown/MouseUp events. However, I feel like I'm trying to invent a bicycle, and I'm not sure that it was the best idea/approach because it has become far more complicated than it should be, especially with the necessity to imitate a drag-n-drop event and describe the logic for a shape's movements following the mouse pointer.

Do someone know any alternative for what I'm trying to achieve?

Thanks in advance.

 

[Peter Bartholomew]

add all such shapes into ChartArea of a blank ChartObject … you cannot move the shapes

I am not sure I understand this. The chart supports mouse move events so it is capable of tracking the movement of the mouse with the shape. Since the tracking is done with an event handler, object protection can be removed and reapplied as you choose.

 

[Alex Martial]

Hi Peter,

Thanks for the response. I meant, that you cannot drag-n-drop a shape within a chart object located on a worksheet if it is locked or if ProtectFormatting = TRUE. MouseMove event is not the best choice in my situation, at least I think so, as you need to listen for MouseDown and MouseUp events to simulate the dragging behavior and a chart object doesn't fire them. The serious problem I have with a blank Label is in the fact that it loses its transparency when you hold a mouse button. In other words, it becomes white while you are holding a mouse button after MouseDown event was fired and till MouseUp event is fired.

What I definitely forgot is that I can unprotect the worksheet since the event was fired and protect it back when everything has been done. However, I still do not understand how to fix the issue when a transparent Label loses its transparency and becomes white. Any ideas?

My initial question was - is there any other simpler way to achieve the functionality, I need (i.e. to protect a shape from any visual formatting and/deleting but keep it draggable) without a necessity to handle mouse events.