Drag & drop between frames in VBA

Question

I'd like to do the following in an Excel/VBA form: There are two Frames on the form, side by side. Let's call them frame1 and frame2. Inside frame1, there are other controls, say labels with text. What I'd like to do is to enable the user to grab one control from one frame and drag it into the other one, smoothly.

However, the control disappears upon dragging it out of frame1. I tried to fiddle around with the ZOrder of the control and the frames but this doesn't help. Does anyone know if it is possible to drag something out of a frame (on a running form, of course) visibly, on top of everything else?

Thanks in advance!

Sorry for the delay, I wasn't at my desk yesterday.

I can show you what I have, but I'm not sure that this is the 'place' where to solve the issue. Anyway, we have frame1 and frame2 as described above, and inside frame1, there is Label1. I've given Label1 the usual mouse events for drag and drop:

Private x_offset%, y_offset%

Private Sub Label1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, _
    ByVal X As Single, ByVal Y As Single)

   If Button = XlMouseButton.xlPrimaryButton Then
     x_offset = X
     y_offset = Y 
   End If

End Sub

Private Sub Label1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, _
    ByVal X As Single, ByVal Y As Single)

  If Button = XlMouseButton.xlPrimaryButton Then
    Label1.Left = Label1.Left + X - x_offset
    Label1.Top = Label1.Top + Y - y_offset
  End If

End Sub

That of course handles only the drag-and-drop part. I've tried setting the ZOrder of Label1 to solve this, but this doesn't actually do much here. Of course the parent of Label1 is the frame it's originally in, but the parent property is read-only.

Meanwhile I've also discovered that the drag-and-drop is not a problem if I place the labels not within frames but e.g. on top of other labels. I guess the frames just have this property of owning everything that's in them, and that ownership is not debatable during runtime. If anyone knows a way around this, I'd be grateful to know, but at least now I have a useable alternative.

Thanks again!

Welcome to SO. Can you provide a sample code of what you have? — Luís Cruz, Nov 11 '14 at 16:54
http://stackoverflow.com/questions/20932711/move-a-dynamic-created-control-on-a-form-using-vba might give you inspiration — ZAT, Nov 12 '14 at 11:53
@milz I've added the code and some more information. Thanks! — Robert, Nov 13 '14 at 08:43

score 2 · Accepted Answer · answered Nov 13 '14 at 21:40

2

Stepping outside the box (no pun intended), we might also ask the question of why we must use a frame... If it is strictly for visual grouping of elements, we can actually create something that looks identical by using labels. Use one label for the back, setting the Caption to blank, and the SpecialEffect to Etched. Add a second label for the caption.

enter image description here

And here is how it looks at run time...

enter image description here

As you can see, there is virtually no visible difference at run time, but using the label control as your frame gives you the same visual element while completely bypassing the control owner issues inherent with the frame control.

Of course there are times when frames are better, such as when using option buttons, but in your case it sounds like maybe a label would achieve your goal quite nicely.

Hope that helps!

answered Nov 13 '14 at 21:40

AdamsTips

1,648
17
22

1

Hi Adam, thanks that's certainly a workable solution! The desired effect was indeed only to create a this-all-belongs-together-impression. – Robert Nov 14 '14 at 09:19
Glad it was helpful! Welcome to StackOverflow, and have fun! – AdamsTips Nov 14 '14 at 13:37
1

P.S. Another quick tip, you can put a single space at the beginning and end of the caption label to make it look the same as the frame caption. I.e. `" Not a Frame "` – AdamsTips Nov 14 '14 at 13:39

Drag & drop between frames in VBA

1 Answers1