Solved

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

Posted on 2012-03-21
3
234 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

706 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

18 Experts available now in Live!

Get 1:1 Help Now