Dragging Cell Contents

xl_psychic

Active Member
Joined
Jan 4, 2004
Messages
359
Hi,

Im back with my queries. This time around its a bit different. Or it might be one of the common ones on the board. In my workbook, I have prevented the user from Copying/Cuting data. However there are cells which are meant for input purpose and hence not locked. I am facing a #REF problem whenever the user drags the contents of such cells.

Is there any way to prevent the user from doing the same.

(OS: WinXP, Excel 2000)
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If you are disabling copy/cut in VBA, this:

Application.CellDragAndDrop = False

will disable drag and drop. Make sure to reset it to what it was afterwards or your user might be unhappy.
 

xl_psychic

Active Member
Joined
Jan 4, 2004
Messages
359
Andrew Poulsom said:
If you are disabling copy/cut in VBA, this:

Application.CellDragAndDrop = False

will disable drag and drop. Make sure to reset it to what it was afterwards or your user might be unhappy.
What does that mean... Will it affect the user in anyway. Please explain.
 

xl_psychic

Active Member
Joined
Jan 4, 2004
Messages
359

ADVERTISEMENT

Juan Pablo González said:
Well, it won't let him drag those cells, as you pointed initially in your question...
I was a bit confused with Andrew's statement. That's what I don't want my users to do. I was thinking that the code would affect the entire Excel application. Please confirm whether the code will only work within that specific workbook or will affect the entire application.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
It will affect the entire application (it's a property of the Application object). That's why I said be sure to reset it to what it was afterwards.
 

xl_psychic

Active Member
Joined
Jan 4, 2004
Messages
359

ADVERTISEMENT

Andrew Poulsom said:
It will affect the entire application (it's a property of the Application object). That's why I said be sure to reset it to what it was afterwards.
Okie, i tried the code but somehow it doesn't seem to work. This is what i have currently .... In my sheet Cell Range C10:H24 is unlocked and unhidden for data entry purposes.

Furthermore I have linked the information filled in C10:E24 onto other sheets. So whenever a user fills in a value in say cell C10 and then drags it to cell C11 then the cell linked to cell C11 shows me a #REF error while the cell linked to cell C10 continues to show me the related information. I want to stop this.

This is code in the backend for now.

Private Sub Worksheet_Activate()
With Me
.EnableOutlining = True
.Protect "password", UserInterfaceOnly:=True
End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Application.CutCopyMode = xlCut Or Application.CutCopyMode = xlCopy Then _

Application.CutCopyMode = False

End If

End Sub

Any workarounds???
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Where did you put the code? It should be in the Worksheet_Activate event procedure. And the code to reset it should be in the Worksheet_Deactivate event procedure.
 

xl_psychic

Active Member
Joined
Jan 4, 2004
Messages
359
Andrew Poulsom said:
Where did you put the code? It should be in the Worksheet_Activate event procedure. And the code to reset it should be in the Worksheet_Deactivate event procedure.
I was trying to merge that with the code in Worksheet_SelectionChange. Is that the right way to do.

Can you please post the code if that's not a problem.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You only need to set it once, not every time the user selects another cell.

Code:
Private Sub Worksheet_Activate() 
   Application.CellDragAndDrop = False
   With Me 
      .EnableOutlining = True 
      .Protect "password", UserInterfaceOnly:=True 
   End With 
End Sub
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,491
Messages
5,764,679
Members
425,229
Latest member
Rashid mahmood

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top