Hi,
In the worksheet, I have cells of different formats - color,
validation, font etc and I wonder if there are any routines to restore
both cells - both the "drag from" area- and the "drop to"- area.

Maybe you can use Selection_change event to remember the "drag from"
area etc.?

I already have a subroutine what recieve the target areas from
Worksheet_change event and correct the areas (it take each cell and
check the row and column to know how the cell should be formated like,
even if I change more than one area). If I could send the two cell
areas effected in draganddrop, I could just use the same routine.

Earlier, I blocked the draganddrop with routines like:
Application.OnKey "^x"
Application.CommandBars("Edit").Controls(3).Enable d = True
Application.CellDragAndDrop = True

But CellDragAndDrop kill the content in the clipboard making the user
not to be able to paste betrween the sheets. Therefore, I try to find
another solutions.

Happy to any suggestions and if possible, code snippets ready to use
....:-)

Kind regards
tskogstrom

May be you can use this to determine a drag/drop operation, as such an
operation fires 2 Worksheet_Change events then the Worksheet_SelectionChange
event.
However, I can't yet diferentiate the first Worksheet_Change event for the
drag/drop from normal entry. Depending what you need to do, this may not
matter.
You will have to Application.CellDragAndDrop = True for drag/drop to be
possible

Dim Cutting As Boolean
Dim DragDropPossible As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Msg As String

With Application
Select Case .CutCopyMode
Case False
If Cutting Then
Msg = "Cut Paste"
Else
If DragDropPossible = True Then
Msg = "Drag & drop operation"
Else
Msg = "Normal Entry"
End If
End If
Case xlCopy
Msg = "Copy Paste"
Case xlCut
'Never this value in a _Change event.
'Need to check for it in _SelectionChange event
End Select
End With
MsgBox Msg

DragDropPossible = True

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cutting = (Application.CutCopyMode = xlCut)
DragDropPossible = False
End Sub

NickHK

"tskogstrom" wrote in message
oups.com...
Hi,
In the worksheet, I have cells of different formats - color,
validation, font etc and I wonder if there are any routines to restore
both cells - both the "drag from" area- and the "drop to"- area.

Maybe you can use Selection_change event to remember the "drag from"
area etc.?

I already have a subroutine what recieve the target areas from
Worksheet_change event and correct the areas (it take each cell and
check the row and column to know how the cell should be formated like,
even if I change more than one area). If I could send the two cell
areas effected in draganddrop, I could just use the same routine.

Earlier, I blocked the draganddrop with routines like:
Application.OnKey "^x"
Application.CommandBars("Edit").Controls(3).Enable d = True
Application.CellDragAndDrop = True

But CellDragAndDrop kill the content in the clipboard making the user
not to be able to paste betrween the sheets. Therefore, I try to find
another solutions.

Happy to any suggestions and if possible, code snippets ready to use
...:-)

Kind regards
tskogstrom

Thanks Nick,
I will check this. I don't need to actually know if there is a drag and
drop, just to be sure both the "drag from areas/ranges/cells" to the
drop to target will be restored with my routine. I need to make a Union
with drag from- and drag to- range and send it as rngCl variable like
below:


Sub Restore_FormatSheetCollection(rngCl As Range)
Dim ar As Range
Dim rn As Range
With rngCl

For Each ar In .Areas
'ar.Merge True ' the True arg merges each row
ar.FormatConditions.Delete

'RANGES
For Each rn In ar
If rn.MergeCells Then
rn.MergeArea.UnMerge
End If

' ALL CELLS
With rn.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 37
End With

As long as it is not a Copy drag/drop (CTRL+Drag/Drop), you can get the drag
range and drop range to do what you want.
If it is Copy drag/drop, then you only get the drop range as the drag range,
although you can follow the initial range in the _SelectionChange event.

NickHK

"tskogstrom" wrote in message
oups.com...
Thanks Nick,
I will check this. I don't need to actually know if there is a drag and
drop, just to be sure both the "drag from areas/ranges/cells" to the
drop to target will be restored with my routine. I need to make a Union
with drag from- and drag to- range and send it as rngCl variable like
below:


Sub Restore_FormatSheetCollection(rngCl As Range)
Dim ar As Range
Dim rn As Range
With rngCl

For Each ar In .Areas
'ar.Merge True ' the True arg merges each row
ar.FormatConditions.Delete

'RANGES
For Each rn In ar
If rn.MergeCells Then
rn.MergeArea.UnMerge
End If

' ALL CELLS
With rn.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 37
End With
.
.
etc ...

/tskogstrom

Hi nick,

I noticed WS_Change event run twice and have tried to work that away -
now I read you initial message more close, and you mention drag and
drop exactly do that. Do you know if there is there is a reason to
this?

It doesn't have any connection to WS_SelectionChange somehow?

Kind regards
tskogstrom


NickHK wrote:
As long as it is not a Copy drag/drop (CTRL+Drag/Drop), you can get the drag
range and drop range to do what you want.
If it is Copy drag/drop, then you only get the drop range as the drag range,
although you can follow the initial range in the _SelectionChange event.

NickHK

"tskogstrom" wrote in message
oups.com...
Thanks Nick,
I will check this. I don't need to actually know if there is a drag and
drop, just to be sure both the "drag from areas/ranges/cells" to the
drop to target will be restored with my routine. I need to make a Union
with drag from- and drag to- range and send it as rngCl variable like
below:


Sub Restore_FormatSheetCollection(rngCl As Range)
Dim ar As Range
Dim rn As Range
With rngCl

For Each ar In .Areas
'ar.Merge True ' the True arg merges each row
ar.FormatConditions.Delete

'RANGES
For Each rn In ar
If rn.MergeCells Then
rn.MergeArea.UnMerge
End If

' ALL CELLS
With rn.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 37
End With
.
.
etc ...

/tskogstrom

That's by design, because a Drag/Drop is really a Cut followed by a Paste.
So you get ;
_SelectionChange, _Change (Cut), _Change (Paste)

A Ctrl+Drag/Drop does not have the _Change (Cut) as you are performing a
Copy/Paste

Follow the _SelectionChange and _Change events for a various combinations of
Cut, Copy, Paste, Drag/Drop to see the sequence.

NickHK

"tskogstrom" wrote in message
ps.com...
Hi nick,

I noticed WS_Change event run twice and have tried to work that away -
now I read you initial message more close, and you mention drag and
drop exactly do that. Do you know if there is there is a reason to
this?

It doesn't have any connection to WS_SelectionChange somehow?

Kind regards
tskogstrom


NickHK wrote:
As long as it is not a Copy drag/drop (CTRL+Drag/Drop), you can get the
drag
range and drop range to do what you want.
If it is Copy drag/drop, then you only get the drop range as the drag
range,
although you can follow the initial range in the _SelectionChange event.

NickHK

"tskogstrom" wrote in message
oups.com...
Thanks Nick,
I will check this. I don't need to actually know if there is a drag
and
drop, just to be sure both the "drag from areas/ranges/cells" to the
drop to target will be restored with my routine. I need to make a
Union
with drag from- and drag to- range and send it as rngCl variable like
below:


Sub Restore_FormatSheetCollection(rngCl As Range)
Dim ar As Range
Dim rn As Range
With rngCl

For Each ar In .Areas
'ar.Merge True ' the True arg merges each row
ar.FormatConditions.Delete

'RANGES
For Each rn In ar
If rn.MergeCells Then
rn.MergeArea.UnMerge
End If

' ALL CELLS
With rn.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 37
End With
.
.
etc ...

/tskogstrom

I can't make it work when i copy drag and drop (ctrl + DragDrop)

By any chance, can you see if it can b e solved?

/Reagrds
tskogstrom


NickHK wrote:
That's by design, because a Drag/Drop is really a Cut followed by a Paste.
So you get ;
_SelectionChange, _Change (Cut), _Change (Paste)

A Ctrl+Drag/Drop does not have the _Change (Cut) as you are performing a
Copy/Paste

Follow the _SelectionChange and _Change events for a various combinations of
Cut, Copy, Paste, Drag/Drop to see the sequence.

NickHK

"tskogstrom" wrote in message
ps.com...
Hi nick,

I noticed WS_Change event run twice and have tried to work that away -
now I read you initial message more close, and you mention drag and
drop exactly do that. Do you know if there is there is a reason to
this?

It doesn't have any connection to WS_SelectionChange somehow?

Kind regards
tskogstrom


NickHK wrote:
As long as it is not a Copy drag/drop (CTRL+Drag/Drop), you can get the
drag
range and drop range to do what you want.
If it is Copy drag/drop, then you only get the drop range as the drag
range,
although you can follow the initial range in the _SelectionChange event.

NickHK

"tskogstrom" wrote in message
oups.com...
Thanks Nick,
I will check this. I don't need to actually know if there is a drag
and
drop, just to be sure both the "drag from areas/ranges/cells" to the
drop to target will be restored with my routine. I need to make a
Union
with drag from- and drag to- range and send it as rngCl variable like
below:


Sub Restore_FormatSheetCollection(rngCl As Range)
Dim ar As Range
Dim rn As Range
With rngCl

For Each ar In .Areas
'ar.Merge True ' the True arg merges each row
ar.FormatConditions.Delete

'RANGES
For Each rn In ar
If rn.MergeCells Then
rn.MergeArea.UnMerge
End If

' ALL CELLS
With rn.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 37
End With
.
.
etc ...

/tskogstrom