Solved

Excel, Drag & Drop

Posted on 2001-07-30
19
6,367 Views
Last Modified: 2012-08-13
Big points, as I'm having terrible difficulties to make a little drag & drop application work.

I have a userform, in which are two frames.

In frame one, are a bunch of Image Controls.

I want the user to be able to drag and drop the images from frame 1 into frame 2 which is empty originally. Frame 2 will then be populated by the images (or rathe a copy from the image control). I need to copy the Image control but see that the controls on a userform in Excel do not even have the Copy property available.

I don't even know if it is possible but any help is welcome. I have the impression Excel does support drag and drop in a very limited way only.

Thanks in advance.
0
Comment
Question by:calacuccia
19 Comments
 
LVL 28

Expert Comment

by:AzraSound
ID: 6335871
i'm not a VBA guy...so what properties/functions/events are emitted by the image control that may aid you in this process?
0
 
LVL 17

Author Comment

by:calacuccia
ID: 6336020
Hi Azra,

Thanx for taking a look ;-) (by the way I did not get an email notification of your comment being added)

The Image control have these events:

BeforeDragOver
BeforeDropOrPaste
MouseDown
Click
DblClick
Error
MouseMove
MouseUp

That's it. The Frame events I'm dragging (or wanting to drag to, at this point I'm barely seeing Image controls dragged in my dreams ;-)), contains about the same events, although there are a lot more there, and I thought the event to use was the BeforeDropOrPaste event...

So I'm looking at using the MouseMove event on the Image control, with the Button = 1 condition in an If ... Then .. loop, but I can't do a Image1.Copy as the Copy method is not available to the Image control....

I've looked at the DataObject which comes with the BeforeDropOrPaste event, but that one barely offers text to be copied...

Quite honestly, I'm quite lost.  I would have thought that it would be easy to implement this, with my experience, but alas.

I hope I gave you the kind of info you were looking for.
0
 
LVL 22

Expert Comment

by:Dreamboat
ID: 6337430
Azra and Cal. Imagine that!!

I've notified Inspector, probably the "other" best VB/VBA guy I know. He ought to sit between you two on the couch.
0
 
LVL 2

Expert Comment

by:Inspector
ID: 6338068
Ugh.

Thanks Dreamboat...for guiding me to this fiasco.

Unfortunately, my help will be very limited here. I only have a deadline to meet and I only have a cursory knowledge of OLE Drag-And-Drop(which won't help here considering that the controls being used do not support this).

An option, may be to build a control in VB, using the VB Controls. That way, you get the OLE Drag-drop options. The problem with that is distributing the Control.

The other problem I see is that you are trying to copy the image to location were it doesn't exist. Without some sort of place holder, I don't think your going to be able to get away with this. Then I again, I've been wrong before.

Maybe if the control already exists at the destination, you may be able to just populate the control with the same properties as the source. You can use the MousebuttonUp event to see if the mouse pointer intersects your destination control.
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 6340468
hmmm...no drag and drop events or properties eh?  i think building a custom control may be the only way if that is indeed the case.
0
 
LVL 17

Author Comment

by:calacuccia
ID: 6355004
Thanks Inspector, Azra, Dream...

I am still looking for a fix, will keep you informed. Inspector, I was silently thinking about the same thing, having empty controls at the possible destination.
0
 
LVL 16

Accepted Solution

by:
sebastienm earned 300 total points
ID: 6369816
I put 3 Image controls, Image1, Image2, Image3, in different frame.
I do the following to change pictures within each other
You could use a dummy (non visible) Image control to make the transition.

Private Sub UserForm_Click()
  MsgBox "Change image1 to image2"
  Set Image1.Picture = Image2.Picture
  MsgBox "change image2 to image3"
  Set Image2.Picture = Image3.Picture

End Sub

-------------------------
Also, are you using Office 2000
If yes, I think you can use control arrays, so that you could load controls at run-time as necessary.

--------------------------

For the drag and drop:
Look at online help in Excel VBA on "Drag-and-Drop Operation Example".
You can use the same technic:
  Drag: put in DataObject : "FrameName-PicureName"
  Drop: From DataObject retrieve name of frame and
        name of picture, then make the picture 'copy' using
        the above example.
-----------------------------

I hope this will help.

Sebastien
0
 
LVL 17

Author Comment

by:calacuccia
ID: 6372213
ThAnks, alot, Sebastien, I was starting to look at the Standard VB Controls, but I believe to have found a possibility with your comment.

Only problem left, how to pass the Text information to Image2.Picture ...

I'm trying with Select Case statement right now.

I don't have Excel 2000 available, and the users won't have it neither probably, so I prefer to remain within the XL97 possibilities.

Will inform you (all) of my advancement.

calacuccia
0
 
LVL 17

Author Comment

by:calacuccia
ID: 6372419
I've been able to get away with it without needing a Select Case loop, but am having a small problem:

When I use the BeforeDropOrPaste event of the destination Image control, to change the Picture property, it won't update on the user form.

The code looks like this:

Private Sub Image17_BeforeDropOrPaste(ByVal Cancel As MSForms.ReturnBoolean, ByVal Action As Long, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal Y As Single, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)
Cancel = True
Effect = fmDropEffectCopy
Set Image17.Picture = Me.Controls(Data.GetText).Picture
End Sub

The strange thing is that if I try to change the picture of another control (not the one being dragged onto) it works correct and shows the new picture on the userform. If I step through code above manually, it shows up OK as well. The only problem is when I apply above code in run-time.

Any idea, anyone?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 28

Expert Comment

by:AzraSound
ID: 6372451
>>If I step through code above manually, it shows up OK as well.

This is generally a result of something requiring a bit of extra time in the background to process before a task can be completed.  Are there any properties that you know of that could be causing this?  Maybe a flag can be set in the above event and you could try and set the picture property in the MouseUp event.  I'm not sure if you would run into problems trying to store the DataObject into a global variable or not, however.
0
 
LVL 17

Author Comment

by:calacuccia
ID: 6372524
I've just tried with an intermediate MyDataObject variable, globally declared, set to Data in the BeforeDropOrPaste event, and then finalised in the MouseUp event, but that did not help neither...

I don't know what could be causing this. It is maybe possible that the fact of working around the limitation of the DataObject (only supports text) is causing this problem.

Does doing things on controls in their own BeforeDropOrPaste event usually interfer in this way?
0
 
LVL 17

Author Comment

by:calacuccia
ID: 6372562
As the Image control in question is within a frame, I've tried the Frame_MouseMove event.

I dragged and dropped the picture onto the Picture (sound nice ;-)), waited a couple of seconds, moved the mouse onto the frame, and still nothing happens. When I do it manually, stepping through code, once again it works.
0
 
LVL 17

Author Comment

by:calacuccia
ID: 6372649
About my last comment ... it did work using the Frame_MouseMove event. Of course it is not nice to only see the result of Drag&Drop when moving away from the control.

But it appears that the MouseUp event is not triggered at the end of Drag&Drop operation. So I used the MouseMove event instead, which seems to work.

I'l keep you posted ;-) (and be attributing some points soon)
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 6372666
try and use the notion of a timer?  in the BeforeDroporPaste event, instantiate a timer, which, say, after half a second or so, sets the picture value.
0
 
LVL 17

Author Comment

by:calacuccia
ID: 6372711
I've tried this at no avail:

    PauseTime = 5   ' Set duration.
    Start = Timer   ' Set start time.
    Do While Timer < Start + PauseTime
        DoEvents    ' Yield to other processes.
    Loop
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 6372749
Do you not have access to a Timer control in VBA?  the problem with the above is that you are still stuck in that same procedure, but waiting.  If you could let the procedure exit, and then some Timer event external to that procedure fires some code, that would be ideal to test.  This would be the synonomous code in VB:


Private Sub Image_BeforeDropOrPaste(...)
  '
  'code

  Timer1.Interval = 1000
  Timer1.Enabled = True
End Sub


Private Sub Timer1_Timer()
  Set Image.Picture = Me.Controls(DataObject.GetText).Picture
  Timer1.Enabled = False
End Sub
0
 
LVL 17

Author Comment

by:calacuccia
ID: 6372799
It is not there, Azra ;-( It looks like a good alternative though.

As I only have VB at home, I'll look into this at home, later (for example, this weekend).
0
 
LVL 17

Author Comment

by:calacuccia
ID: 6383618
I have finally (at least, at the end of this part of the project, the Drag & Drop thing in Excel) decided to go another way, because of the Update problems of the form.

I have now used the idea of SebastienM (Also, are you using Office 2000
If yes, I think you can use control arrays, so that you could load controls at run-time as necessary.) but in Office 97, by making a class module which handles all the drag & drop events for the Image controls, which means I can also create the controls in frame 2 on the run, as well as deleting and repositioning them on the run.

I am very thankfull to all here, this question has learned me alot:

- Use of class event to create controls on the run, assign event procedures to them
- Use of class event to assign one single event procedure to an undefined number of controls
- Programmation of a drag & drop procedure (never done this before)
- Getting around the limitations of the Office 97 DataObject, which does only support text
- Use of the timer control (although not used in the end)

....

I will post the code, for future PAQ readers in my next comment, but first want to thank Inspector and Dreamboat for their contribution, and Azrasound for his continuous support. I'll open a separate 100 pt quesion for you Azra, and will award the 300 pts here to sebastienM.

Thanks again
calacuccia
0
 
LVL 17

Author Comment

by:calacuccia
ID: 6383631
Azra, your points are here http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=msoffice&qid=20168169

My code is as follows:

- In the Userform, containing
    - Frame1, with 16 Image controls (Image1 to Image16)
    - Frame2, being empty, and populated through the BeforeDropOrPaste event with Image controls, called "Squarex", x being the serial number.
    - CommandButton2 which will delete a control when dragged onto

I have this code in this Userform:

Option Base 1
Dim mCtrl() As New MImage 'This is the class declaration
Dim ctrlCount As Integer, ctrlIndex As Integer
Dim ctrlArray() As Integer, tmpArray() As Integer
Const nImages As Integer = 16


Private Sub CommandButton2_BeforeDragOver(ByVal Cancel As MSForms.ReturnBoolean, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal Y As Single, ByVal DragState As Long, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)
'Procedure to remove controls from Frame2
If Left(Data.GetText, 2) = "Sq" Then
    Cancel = True
    Effect = fmDropEffectMove
End If
End Sub

Private Sub CommandButton2_BeforeDropOrPaste(ByVal Cancel As MSForms.ReturnBoolean, ByVal Action As Long, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal Y As Single, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)
Dim mFlag As Boolean
mFlag = False
Cancel = True
Effect = fmDropEffectMove
ctrlNumber = CInt(Right(Data.GetText, Len(Data.GetText) - 6))
Me.Controls.Remove Data.GetText
For i = 1 To ctrlCount - nImages
    If mFlag = True Then
        ctrlArray(i - 1) = ctrlArray(i)
        Me.Controls("Square" & ctrlArray(i - 1)).Left = Me.Controls("Square" & ctrlArray(i - 1)).Left - 36
    End If
    If ctrlArray(i) = ctrlNumber Then
        mFlag = True
    End If
Next i
ctrlCount = ctrlCount - 1
If ctrlCount - nImages <> 0 Then ReDim Preserve ctrlArray(ctrlCount - nImages)
End Sub


Private Sub Frame2_BeforeDragOver(ByVal Cancel As MSForms.ReturnBoolean, ByVal Control As MSForms.Control, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal Y As Single, ByVal State As Long, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)
If Left(Data.GetText, 2) <> "Sq" Then
Cancel = True
Effect = fmDropEffectCopy
End If
End Sub

Private Sub Frame2_BeforeDropOrPaste(ByVal Cancel As MSForms.ReturnBoolean, ByVal Control As MSForms.Control, ByVal Action As Long, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal Y As Single, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)
Dim newCtrl As Image
Cancel = True
Effect = fmDropEffectCopy
'Adds control at run time
ctrlIndex = ctrlIndex + 1
ctrlCount = ctrlCount + 1
ReDim Preserve ctrlArray(ctrlCount - nImages)
ReDim Preserve tmpArray(ctrlCount - nImages)
Set newCtrl = Frame2.Controls.Add("Forms.Image.1", "Square" & ctrlIndex, True)
'Set width & height properties
newCtrl.Height = 36
newCtrl.Width = 36
'Determine position of control, Top is always 30
'Depends on where the object is dropped
newCtrl.Top = 30
If X <= 12 + 36 Then
    newCtrl.Left = 12
    If ctrlCount > nImages + 1 Then
        For i = ctrlCount - nImages - 1 To 1 Step -1
            Me.Controls("Square" & ctrlArray(i)).Left = Me.Controls("Square" & ctrlArray(i)).Left + 36
            ctrlArray(i + 1) = ctrlArray(i)
        Next i
        ctrlArray(1) = ctrlIndex
    Else
        ctrlArray(1) = ctrlIndex
    End If
ElseIf X > 12 + 36 * (ctrlCount - nImages - 1) Then
    newCtrl.Left = 12 + 36 * (ctrlCount - nImages - 1)
    ctrlArray(ctrlCount - nImages) = ctrlIndex
Else
    mNum = Int((X - 12) / 36)
    newCtrl.Left = mNum * 36 + 12
    If ctrlCount - nImages > mNum + 1 Then
        For i = ctrlCount - nImages - 1 To mNum + 1 Step -1
            Me.Controls("Square" & ctrlArray(i)).Left = Me.Controls("Square" & ctrlArray(i)).Left + 36
            ctrlArray(i + 1) = ctrlArray(i)
        Next i
    End If
    ctrlArray(mNum + 1) = ctrlIndex
End If
newCtrl.PictureSizeMode = fmPictureSizeModeStretch
Set newCtrl.Picture = Me.Controls(Data.GetText).Picture
newCtrl.Tag = Me.Controls(Data.GetText).Tag
newCtrl.ControlTipText = newCtrl.Name
ReDim Preserve mCtrl(nImages + ctrlIndex)
Set mCtrl(nImages + ctrlIndex).MImageCtrl = newCtrl
End Sub


Private Sub UserForm_Initialize()
'This is the trick, declaring all the Class objects mCtrl(i)
ctrlIndex = 0
ctrlCount = nImages
ReDim Preserve mCtrl(nImages)
For i = 1 To nImages
    Set mCtrl(i).MImageCtrl = Me.Controls("Image" & i)
Next i
End Sub

In the class module, MImage, I have following code:

Public WithEvents MImageCtrl As MSForms.Image

Private Sub MImageCtrl_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim MText As String
If Button = xlSecondaryButton Then
    MText = MImageCtrl.Name
    UserForm1.Hide
    TmpCode MText
End If
End Sub

Private Sub MImageCtrl_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
If Button = 1 Then
    Dim myDataObj As New DataObject
    Dim Effect As Integer
    myDataObj.SetText MImageCtrl.Name
    Effect = myDataObj.StartDrag
End If
End Sub


Regards
calacuccia
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction Perhaps more familiar to developers who primarily use VBScript than to developers who tend to work only with Microsoft Office and Visual Basic for Applications (VBA), the Dictionary is a powerful and versatile class, and is useful …
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now