[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel drag and drop

Posted on 2011-05-02
6
Medium Priority
?
701 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:bruno71
  • 3
  • 3
6 Comments
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 35506475
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
 

Author Comment

by:bruno71
ID: 35506657
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
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 35507214
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 38

Accepted Solution

by:
Gerwin Jansen, EE MVE earned 2000 total points
ID: 35508179
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
 

Author Comment

by:bruno71
ID: 35706115
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
 

Author Closing Comment

by:bruno71
ID: 35706138
Not quite the drag-and-drop I wanted, but it works really well.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

864 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