SUBJECT: Error handler if copy area different from paste area.

I am writing a macro that pastes cells from the clipboard to a spreadsheet.
But I want the macro to run ONLY if the number of columns already on the
clipboard is EXACTLY the same as a number that I plan to specify in code.

In other words, I need some way of determining how many columns are on the
clipboard BEFORE I commit to the paste operation.

If the number of columns that are about to be pasted is not what I expect,
then I will write an error handler to abort the paste operation.

So, my question is - - -

How can I tell how many columns will be pasted BEFORE I commit to the paste
operation?

Paul S. Natanson +1-908-630-0406 paul628 at concentric dot net
December 1, 2006

Paul,
Something like this:

Dim PreviousRange As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not PreviousRange Is Nothing Then
Select Case Application.CutCopyMode
Case False
Debug.Print "_SelectionChange : False"
Case xlCopy
Debug.Print "_SelectionChange : Copied " &
PreviousRange.Columns.Count
Case xlCut
Debug.Print "_SelectionChange : Cut"
End Select
End If

Set PreviousRange = Target

End Sub

You can set the .CutCopy mode also if you wish to cancel a copy/cut.

NickHK

"Paul S. Natanson" (Remove xxx's for real
address.) wrote in message ...
SUBJECT: Error handler if copy area different from paste area.

I am writing a macro that pastes cells from the clipboard to a
spreadsheet.
But I want the macro to run ONLY if the number of columns already on the
clipboard is EXACTLY the same as a number that I plan to specify in code.

In other words, I need some way of determining how many columns are on the
clipboard BEFORE I commit to the paste operation.

If the number of columns that are about to be pasted is not what I expect,
then I will write an error handler to abort the paste operation.

So, my question is - - -

How can I tell how many columns will be pasted BEFORE I commit to the
paste
operation?

Paul S. Natanson +1-908-630-0406 paul628 at concentric dot net
December 1, 2006

NickHK: Your suggestion (below) is the best that I have heard so far. But
I forgot to tell you that the stuff that was copied to the clipboard is
coming from a different Excel file. Is there any way to tell how many
columns of data are on the clipboard before I paste them? Paul December 4,
2006.

xxxxxxxxxxxxxxxxxxxxxx

"NickHK" wrote in message
...
Paul,
Something like this:

Dim PreviousRange As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not PreviousRange Is Nothing Then
Select Case Application.CutCopyMode
Case False
Debug.Print "_SelectionChange : False"
Case xlCopy
Debug.Print "_SelectionChange : Copied " &
PreviousRange.Columns.Count
Case xlCut
Debug.Print "_SelectionChange : Cut"
End Select
End If

Set PreviousRange = Target

End Sub

You can set the .CutCopy mode also if you wish to cancel a copy/cut.

NickHK

"Paul S. Natanson" (Remove xxx's for real
address.) wrote in message ...
SUBJECT: Error handler if copy area different from paste area.

I am writing a macro that pastes cells from the clipboard to a
spreadsheet.
But I want the macro to run ONLY if the number of columns already on the
clipboard is EXACTLY the same as a number that I plan to specify in code.

In other words, I need some way of determining how many columns are on
the
clipboard BEFORE I commit to the paste operation.

If the number of columns that are about to be pasted is not what I
expect,
then I will write an error handler to abort the paste operation.

So, my question is - - -

How can I tell how many columns will be pasted BEFORE I commit to the
paste
operation?

Paul S. Natanson +1-908-630-0406 paul628 at concentric dot net
December 1, 2006

In that case, maybe a combination of this code and application level events.
See
www.cpearson.com/excel/AppEvent.htm

Or paste the clipboard somewhere suitable and count the columns:
With Worksheets("Whatever").Range("A1")
.PasteSpecial xlPasteValues
MsgBox .CurrentRegion.Columns.Count
End With

NickHK

"Paul S. Natanson" (Remove xxx's for real
address.) wrote in message ...
NickHK: Your suggestion (below) is the best that I have heard so far.
But
I forgot to tell you that the stuff that was copied to the clipboard is
coming from a different Excel file. Is there any way to tell how many
columns of data are on the clipboard before I paste them? Paul December
4,
2006.

xxxxxxxxxxxxxxxxxxxxxx

"NickHK" wrote in message
...
Paul,
Something like this:

Dim PreviousRange As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not PreviousRange Is Nothing Then
Select Case Application.CutCopyMode
Case False
Debug.Print "_SelectionChange : False"
Case xlCopy
Debug.Print "_SelectionChange : Copied " &
PreviousRange.Columns.Count
Case xlCut
Debug.Print "_SelectionChange : Cut"
End Select
End If

Set PreviousRange = Target

End Sub

You can set the .CutCopy mode also if you wish to cancel a copy/cut.

NickHK

"Paul S. Natanson" (Remove xxx's for
real
address.) wrote in message
...
SUBJECT: Error handler if copy area different from paste area.

I am writing a macro that pastes cells from the clipboard to a
spreadsheet.
But I want the macro to run ONLY if the number of columns already on
the
clipboard is EXACTLY the same as a number that I plan to specify in
code.

In other words, I need some way of determining how many columns are on
the
clipboard BEFORE I commit to the paste operation.

If the number of columns that are about to be pasted is not what I
expect,
then I will write an error handler to abort the paste operation.

So, my question is - - -

How can I tell how many columns will be pasted BEFORE I commit to the
paste
operation?

Paul S. Natanson +1-908-630-0406 paul628 at concentric dot
net
December 1, 2006