Dragging Cell Contents

[xl_psychic]

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)

 

[Andrew Poulsom]

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]

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.

 

[Juan Pablo González]

Well, it won't let him drag those cells, as you pointed initially in your question...

 

[xl_psychic]

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]

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]

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]

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]

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]

You only need to set it once, not every time the user selects another cell.

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