Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2012-03-21
3
Medium Priority
?
354 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

604 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