Excel drag and drop

Is there a way in Excel to allow someone to drag-and-drop a cell to move it around, instead of pointing at the border to do it?  I want the user to be able to click anywhere on a cell to drag and drop it...instead of clicking on the cell, then pointing at the border, then drag and drop.  So rather than highlighting an area, it would drag and drop the cell.

Part 2 of this question...when the user clicks on the cell to drag and drop it, is there a way to take the 3 cells to the left with it.  For example, if the user clicks on cell E5 to move it, I want cells B5, C5, & D5 to move with it (via the drag and drop).

~bruno71
bruno71Asked:
Who is Participating?
 
Gerwin Jansen, EE MVETopic Advisor Commented:
I've created a working sample for you, on a new Excel workbook, right click Sheet1, select 'View Code' and paste this:

Option Explicit
Public switch As Boolean
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If switch = True Then
        Call MyProcedurePaste(Target)
    Else
        Call MyProcedureCut(Target)
    End If
End Sub
Sub MyProcedureCut(ByVal rCut As Range)
    Dim numColumns As Integer
    switch = Not switch
    numColumns = Selection.Columns.Count
    Selection.Resize(1, numColumns + 2).Select
    Selection.Cut
End Sub
Sub MyProcedurePaste(ByVal rPaste As Range)
    switch = Not switch
    Paste
End Sub

Open in new window


Type some values in 3 columns on 1 row, select first value (click), copy (double click), select another cell and double click again (paste). Note: the +2 is the increased width.

Save the workbook as Excel Macro Enabled Workbook.
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
Selecting a cell in Excel is done by clicking with the mouse. You'd have to change that standard Excel behaviour first to achieve what you want. I've reconfigured Excel's double-click function a while ago to call a macro that does whatever you want it to. It this in line of what you want? What is your Excel version btw?
0
 
bruno71Author Commented:
That sounds like what I want to do.  Click once to select.  Double-click to edit. Click-and-drag to move (even if cell was previously unselected).  We're using Excel 2007.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Gerwin Jansen, EE MVETopic Advisor Commented:
Click to select = standard  behaviour, double click van be modified using Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) that will enable you to call some procedure to to what you want. You'd have to write some code there. First double click would select the data to be moved and the 2nd double click to 'paste' it again. Here's a sample of the BeforeDoubleClick in Excel2007:

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Value = "GO" Then
Cancel = True
Call MyProcedure
End If
End Sub
Sub MyProcedure()
    MsgBox "GO double clicked"
End Sub

Open in new window

0
 
bruno71Author Commented:
Thanks.  Here is what I ended up doing...

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Trim(Target.Value) <> "" Then
    Cancel = True
    'Range(Target.Offset(0, -3), Target).Select
    Range(Target.Offset(0, -3), Target).Cut
Else
    Cancel = True
    ActiveSheet.Paste Destination:=Range(Target.Offset(0, -3), Target)
End If

End Sub

Open in new window

0
 
bruno71Author Commented:
Not quite the drag-and-drop I wanted, but it works really well.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.