Paste special function in macros fail if we disable application cellDrap Drop

[borkarsameet]

Hi,
I have an excel which has a inhouse format. When a user tires to
paste data onto to the excel he should only be pasting values and not
disturb the format / formulas in the excel.I have implemented this by
using paste special when the user tries to paste.This can be done by
capturing the paste keys and calling paste special instead.
In addition to this I also need to diable the Cell Drag drop
functionality so that no one should be able to drag the cells down and
disturb the formulas.So I used the Application.CellDragDrop = false.
This does throws error when paste special is called saying the paste
special function of the range failed.If I comment the
Application.CellDragDrop = false line it works fine.I have given the
code below. Any help appreciated.
Private Sub Workbook_Activate()
Application.CellDragAndDrop = false
Application.OnKey "^v", "Sheet1.Pastewithoutformatting" 'Added new
function
End Sub
Private Sub Workbook_Deactivate()
Application.CellDragAndDrop = true
End Sub

Sub PasteWithoutFormatting()
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlPasteSpecialOperationNone, SkipBlanks:=False,
Transpose:=False
Exit Sub

 

[Jim Rech]

I don't think there is any way that the D&D setting affects Paste Special.
So I'd check for something else being the problem - not being in Copy mode,
selection (paste selection) not being compatible with the range being
copied, sheet protection,...

Odd that your "false" and "true" below are not capitalized.

--
Jim
| Hi,
| I have an excel which has a inhouse format. When a user tires to
| paste data onto to the excel he should only be pasting values and not
| disturb the format / formulas in the excel.I have implemented this by
| using paste special when the user tries to paste.This can be done by
| capturing the paste keys and calling paste special instead.
| In addition to this I also need to diable the Cell Drag drop
| functionality so that no one should be able to drag the cells down and
| disturb the formulas.So I used the Application.CellDragDrop = false.
| This does throws error when paste special is called saying the paste
| special function of the range failed.If I comment the
| Application.CellDragDrop = false line it works fine.I have given the
| code below. Any help appreciated.
| Private Sub Workbook_Activate()
| Application.CellDragAndDrop = false
| Application.OnKey "^v", "Sheet1.Pastewithoutformatting" 'Added new
| function
| End Sub
| Private Sub Workbook_Deactivate()
| Application.CellDragAndDrop = true
| End Sub
|
| Sub PasteWithoutFormatting()
| Selection.PasteSpecial Paste:=xlPasteValues,
| Operation:=xlPasteSpecialOperationNone, SkipBlanks:=False,
| Transpose:=False
| Exit Sub
|

 

[borkarsameet]

I don't think there is any way that the D&D setting affects Paste Special.
So I'd check for something else being the problem - not being in Copy mode,
selection (paste selection) not being compatible with the range being
copied, sheet protection,...

Odd that your "false" and "true" below are not capitalized.

--

| Hi,
|   I have an excel which has a inhouse format. When a user tires to
| paste data onto to the excel he should only be pasting values and not
| disturb the format / formulas in the excel.I have implemented this by
| using paste special when the user tries to paste.This can be done by
| capturing the paste keys and calling paste special instead.
| In addition to this I also need to diable the Cell Drag drop
| functionality so that no one should be able to drag the cells down and
| disturb the formulas.So I used the Application.CellDragDrop = false.
| This does throws error when paste special is called saying the paste
| special function of the range failed.If I comment the
| Application.CellDragDrop = false line it works fine.I have given the
| code below. Any help appreciated.
| Private Sub Workbook_Activate()
| Application.CellDragAndDrop = false
| Application.OnKey "^v", "Sheet1.Pastewithoutformatting" 'Added new
| function
| End Sub
| Private Sub Workbook_Deactivate()
| Application.CellDragAndDrop = true
| End Sub
|
| Sub PasteWithoutFormatting()
|    Selection.PasteSpecial Paste:=xlPasteValues,
| Operation:=xlPasteSpecialOperationNone,  SkipBlanks:=False,
| Transpose:=False
|    Exit Sub
|

Ya I was of the same opinion that the D &D should not have any impact
over pastespecial.but the funny thing is the paste special works
absolutely fine if I remove / comment the D & D code. i.,e I comment
Application.CellDragAndDrop = True