Event triggered when a shape is moved

[justin-uk]

I have searched the forums for an answer to this and haven't found anything, but I am forever hopeful!

Is there a way to trigger a macro when a shape is moved by the user? If I set the OnAction property for a shape, then the cursor changes to a hand when it is over the shape, and I can detect a mouse click on the shape, but I cannot drag and drop the shape. If I reset the OnAction property to "" (empty quotes), then I can drag and drop again within the worksheet, but I can't trigger the macro.

Worksheet_Change or Worksheet_SelectionChange are not triggered by this event.

People have suggested using timers to continuously poll the shapes and determine their locations, but is there an easier way?

What I am trying to do is create a sheet where the user can visually move around objects (in this case representing employees) and deposit them in various zones. The spreadsheet would then apply certain attributes to the shape i.e. change colour according to where the shape is and if it is in an unsuitable zone. I can do all of this, but I want it to work the instant the shape is moved, not rely on the user to hit a button.

Thanks,

Justin

 

[Jaafar Tribak]

Hi Tom.

I was thinking maybe using an Interface Class to create cloned UserForms that look like shapes embeeded in the worksheet could be a good solution.

1-Create a common Class Interface to set the forms Properties.
2-Set up the UserForm (Set the worksheet as the Window Parent - Remove caption -Set the initial location -Set the exclusion drop worksheet ranges - Set mouse capture- Assign colour and text ... etc.)

This,i think, would also avoid the need for loops, timers etc and would ensure smoother dragging. Just an idea

I'll give this a try if I get the time.

Regards.

 

[Legacy 98055]

Hi Jafaar. Sort of. The problem is that userforms do not scroll with the worksheet. The only way I have figured out how to make a userform maintain a static postition is by using an embedded listbox as the parent. I am actually using forms to do this but in a different manner than you are suggesting... Run Example in sheet1. When you click and drag on any of the image controls, a userform mocks the movement almost perfectly.

<A HREF="http://cid-ea73b3a00e16f94f.skydrive.live.com/self.aspx/Mr%20Excel%20Example/DragNDropImageControl2.zip" TARGET="_blank">DragNDropImageControl2.xls.zip</A>

As for the embedded userforms, I was wondering if you could apply styles to the userform to make it behave in the same way an embedded control would behave. I doubt it, but have not tried it yet...

 

[Jaafar Tribak]

Hi Jafaar. Sort of. The problem is that userforms do not scroll with the worksheet. The only way I have figured out how to make a userform maintain a static postition is by using an embedded listbox as the parent. I am actually using forms to do this but in a different manner than you are suggesting... Run Example in sheet1. When you click and drag on any of the image controls, a userform mocks the movement almost perfectly.

DragNDropImageControl2.xls.zip

As for the embedded userforms, I was wondering if you could apply styles to the userform to make it behave in the same way an embedded control would behave. I doubt it, but have not tried it yet...

Tom.

You are right. I thought that by making the userform a child of the Worksheet Window I was going to be able to embeed it like a Control but thatb is not the case and scrolling the worksheet doesn't scroll the userform .

I downloaded your example workbook but when I click on an image control nothing happens ! I haven't studied your code yet.

I am still thinking about using some other tricks to achieve this

Regards.

 

[Legacy 98055]

Run Sub Example in sheet1 to create the objects...

 

[Legacy 98055]

The Employee objects are initialized on Workbook_Open in this example. Still not finished.

<A HREF="http://cid-ea73b3a00e16f94f.skydrive.live.com/self.aspx/Mr%20Excel%20Example/DragNDropImageControl3.zip" TARGET="_blank">DragNDropImageControl3.xls.zip</A>

 

[Legacy 98055]

Justin. This is all I can do for now but there should be enough in the example to give you an idea. I don't know your level and did not bother with many comments. If you have any questions, just post here to this thread.

<A HREF="http://cid-ea73b3a00e16f94f.skydrive.live.com/self.aspx/Mr%20Excel%20Example/DragNDropImageControl4.zip" TARGET="_blank">DragNDropImageControl4.xls.zip</A>

 

[Jaafar Tribak]

The Employee objects are initialized on Workbook_Open in this example. Still not finished.

DragNDropImageControl3.xls.zip

Thanks Tom.

The dragging is so smooth now . Nice !

How about dinamically changing the cursor icon to something more suugestive like a grabbing hand cursor when mouse-hoovering the image controls?

Regards.

 

[Legacy 98055]

Have at it Mr. Jaafar.

 

[mahmed1]

Thanks Tom.

The dragging is so smooth now . Nice !

How about dinamically changing the cursor icon to something more suugestive like a grabbing hand cursor when mouse-hoovering the image controls?

Regards.

Hi Jaafar/Tom

can you please help me with a similar query to the code you guys have been looking at

I have multiple shapes on top of a shape

i have a dragto shape. I want the user to be able drag and move any shape just like the way excel currently allows you to do but When it is dragged over a certain shape - i want to fire of a macro that hides that dragto shape and displays a gif file instead which i have

so my aim is

Press a button that fades and displays everything in my group named groupArea
Able to drag certain shapes in the group (all these shapes are ontop of an image and ive grouped them by naming it (groupArea)
when dragged over the dragto shape
hide dragto shape
display gif file instead (loading gif file) which then carrys on with my other part of macro
once Macro is finished, fade the transparency of all shapes in the groupArea (all shapes) to 100 using do events)

this is to give it an affect that once a shape is dragged into the area - it displays a loading gif image - macro finishes and then every fades away (all these shapes are on top of my charts) so ultimately my charts will update and display once macro finishes

can you please help me

thank you

 

[Jaafar Tribak]

Hi Jaafar/Tom

can you please help me with a similar query to the code you guys have been looking at

I have multiple shapes on top of a shape

i have a dragto shape. I want the user to be able drag and move any shape just like the way excel currently allows you to do but When it is dragged over a certain shape - i want to fire of a macro that hides that dragto shape and displays a gif file instead which i have

so my aim is

Press a button that fades and displays everything in my group named groupArea
Able to drag certain shapes in the group (all these shapes are ontop of an image and ive grouped them by naming it (groupArea)
when dragged over the dragto shape
hide dragto shape
display gif file instead (loading gif file) which then carrys on with my other part of macro
once Macro is finished, fade the transparency of all shapes in the groupArea (all shapes) to 100 using do events)

this is to give it an affect that once a shape is dragged into the area - it displays a loading gif image - macro finishes and then every fades away (all these shapes are on top of my charts) so ultimately my charts will update and display once macro finishes

can you please help me

thank you

Hi,

I am not sure I can offer a tailored answer to the specifics of the (rather involved) scenario you have described but I'll see if I can, at least, come up with a pseudo-event that is triggered when a worksheet shape is moved while still preserving the Macro attached to the shape as requested by the OP in his first post.