?
Solved

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

Posted on 2012-03-21
3
Medium Priority
?
299 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 8

Accepted Solution

by:
wchh earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

771 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