Solved

Prevent users from dragging a cell contents to a new location.

Posted on 2012-03-21
3
238 Views
Last Modified: 2012-03-22
Is there a way with a macro that I can disable users from selecting a cell and then dragging it to a new location.  It is screwing up my formulas.  I'm sure I'm not the first person to experience this.  How do you over come this?
0
Comment
Question by:BigWill5112
3 Comments
 
LVL 8

Accepted Solution

by:
wchh earned 500 total points
ID: 37750818
Try:
Exel Option: Advance->Edit Option: Check off 'Enable fill handle and cell drag and drop'
0
 
LVL 8

Expert Comment

by:csoussan
ID: 37750930
The suggestion above will work but it will disbale the fill handle and the cell drag and drop for all workbooks.  An alternative is to protect (aka "lock") a worksheet to prevent your users from changing, moving, or deleting data in just your selected worksheet.  

Note, by default, when you protect a worksheet, all the cells on the worksheet are locked, and users cannot make any changes to a locked cell. Before you protect a worksheet, you will need to unlock all the cells you want users to be able to change or enter data in. In your example, you would unlock all cells except the one you don't want moved.  To unlock all cells except one:

1. Click on the tab of the worksheet that you want to protect and select all cells (CTRL-A).

2. Right-click over the selected worksheet --> select Format cells --> Protection tab --> uncheck the Locked checkbox --> click OK.

3. Select the cell you want to protect --> right-click the cell --> select Format cells --> Protection tab --> check the Locked checkbox --> click OK. (At this point you have unlocked everything except the cell you want to protect.)

4.On the Review tab, in the Changes group, click Protect Sheet.

5.In the Allow all users of this worksheet to list, select the elements that you want users to be able to change.

6.In the Password to unprotect sheet box, type a password --> click OK, --> retype the password to confirm it click OK.  The password is optional.

Hope this helps.
0
 

Author Closing Comment

by:BigWill5112
ID: 37752132
This is sort of what I needed but I didn't want it to be applicable to the application, just the particular workbook.

I was also aware of protecting sheets.

I think I will be really happy with the below.

http://vbadud.blogspot.com/2008/04/disable-drag-drop-of-cells-in-excel-vba.html
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
First Blank Cell in a range 7 33
DBF to ... Converter 5 29
Auto populate in Cascade dropdown 3 25
Excel Save As Status Box will not go away 6 16
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

932 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

14 Experts available now in Live!

Get 1:1 Help Now