asked on

Excel VBA - drag and drop cell to change order

I'm trying to create a manually 'drag and drop a cell to sort some rows of data'.

To do this, I need to capture into variable iFromRow the row dragged from (eg. iFromRow = 5 when 'Fred' is dragged to A7), and capture into variable iToRow the row dragged to (eg. iToRow = 7 in this example).

I played around with Worksheet_SelectionChange but got nowhere :(

Thanks for any ideas.

(I can do the actual 'sort' by programmatically Cut/Insert Row by using those two row numbers)

Martin Liss

Excel doesn't supply the events you need for a cell but I have an idea. To implement it I'd need to know if there's a limit to the range where it might be dragged, and if so can you tell me that range?

SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

[ fanpages ]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hindersaliva

ASKER

Hi All,
I got it to work by Drag and Drop.

    Dim iFromRow As Integer
    Dim iToRow As Integer
    Dim strSwitch1 As String
    Dim strSwitch2 As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Column = 2 Then

        iFromRow = Target.Row
        iFromColumn = 2
        strSwitch1 = "Yes"

    End If

    If Target.Column = 1 Then

        iToRow = Target.Row
        iToColumn = 1
        strSwitch2 = "Yes"

    End If

    If strSwitch1 = "Yes" And strSwitch2 = "Yes" Then
        Call DragAndDropToChangeOrder(iFromRow, iToRow)
        strSwitch1 = ""
        strSwitch2 = ""
    End If

End Sub

Open in new window

Sub DragAndDropToChangeOrder(iFromRow As Integer, iToRow As Integer)

    If iFromRow <> iToRow Then
    
        Cells(iFromRow, 1).EntireRow.Cut
        Cells(iToRow, 1).EntireRow.Insert Shift:=xlDown
                
    End If

    If iFromRow < iToRow Then
        Cells(iToRow, 1).Cut Destination:=Cells(iToRow - 1, 2)
    Else
        Cells(iToRow + 1, 1).Cut Destination:=Cells(iToRow, 2)
    End If

End Sub

Open in new window

hindersaliva

ASKER

Here's my example file.
DragDropRunningOrder.xlsm

hindersaliva

ASKER

The help from both Experts were useful in solving my problem.
Thanks.

Martin Liss

I think you have some problems with your solution. First, like my solution, I don't see where it's drag and drop, and second, when I click on say "Fred" in B8 and then click in column "A", all that seems to happen is that B8 is cleared.

In any case you're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014

hindersaliva

ASKER

Ah. Place cursor on cell 'Fred'. Drag it to another row, but to Column 1 of that row. Release. The order should change now.

I'll see if I can post a video on here.

Martin Liss

Please repost the workbook because the one you posted doesn't do anything when I follow your instructions.

hindersaliva

ASKER

Here's a 30 sec video of how it works.
https://www.youtube.com/watch?v=z2l-ezdd9sg

[ fanpages ]

^ Well done :)

Martin Liss

OK, I see that your not actually dragging and dropping but instead clicking twice, but there's a problem in the code in the workbook you posted. In that workbook you have this

Sub DragAndDropToChangeOrder(iFromRow As Integer, iToRow As Integer)

    If iFromRow <> iToRow Then
    
        Cells(iFromRow, 1).EntireRow.Cut
        Cells(iToRow, 1).EntireRow.Insert Shift:=xlDown
                
    End If

    If iFromRow < iToRow Then
        Cells(iToRow, 1).Cut Destination:=Cells(iToRow - 1, 2)
    Else
        Cells(iToRow + 1, 1).Cut Destination:=Cells(iToRow, 2)
    End If

End Sub

Open in new window

and lines 10 to 14 are a problem. Either line 11 or 13 will always be executed and what line 11 does for example is to cut the value in column 1, which is blank, and paste it over the cell in column 2 so that cell is blanked out. You don't seem to need lines 10 to 14 at all.

hindersaliva

ASKER

Martin, I'm dragging the cell from where it is to the cell in column 1. Ie. Hovering over the edge. I'll do another video to make clear.

hindersaliva

ASKER

Video 2 - hopefully clearer
https://www.youtube.com/watch?v=1uKtICV7IDw

hindersaliva

ASKER

Martin, I need lines 10 to 14 because the user would have 'dragged' the cell to a new location in Col 1. This code Cut/Pastes it back to where it should be after the 'switch'.

Martin Liss

OK I see what you are doing but make sure you play by the "rules" or otherwise if you do something like I did which was to click in cell B7 and then click in cell A8, cell B7 will be cleared.