Solved

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

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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

724 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