Forcing to paste on a specific row
 

Good day everyone,

Is it possible to force the user to paste on specific rows? As the
user will copy info from a page of a non Windows application and then
"Special paste Unicode text" on an excel spreadsheet, I'd like to
force him to paste only on specific rows, i.e. 1, 75, 150, 225, 300,
etc....

In other words, if there's something in cell A1, the you must paste on
cell A75, then the third time on cell A150 etc....

Is it possible?

Thanks for your time

Denys

Forcing to paste on a specific row
 

Denys,
You should add error trapping to this quickly tested code.
It is restricted to the worksheet that the code is on, due to the:
Private Sub Worksheet_Deactivate()
Set mPasteSpecialButton = Nothing
End Sub

If you want it to apply to all/more that 1 sheet, move to the Workbook_Open
and test the sheet involved.
Note that PasteSpecial in Unicode Text in a multiple of ROWSTEP is all that
can occur. Adjust the behaviour if this is too restrictive.

<Currently Worksheet code
Private WithEvents mPasteSpecialButton As CommandBarButton
Private LastPasteMultiple As Long

Private Const ROWSTEP As Long = 75
Private Const PASTESPECIAL_ID As Long = 755

Private Sub mPasteSpecialButton_Click(ByVal Ctrl As Office.CommandBarButton,
CancelDefault As Boolean)
Dim OKRow As Long

If TypeName(Selection) = "Range" Then
If LastPasteMultiple = 0 Then
OKRow = 1
Else
OKRow = LastPasteMultiple * ROWSTEP
End If

If Selection.Row < OKRow Then
MsgBox "Can only Paste Special on row " & OKRow
Else
ActiveSheet.PasteSpecial Format:="Unicode Text"
LastPasteMultiple = LastPasteMultiple + 1
End If
End If
CancelDefault = True
End Sub

Private Sub Worksheet_Activate()
Set mPasteSpecialButton = Application.CommandBars.FindControl(,
PASTESPECIAL_ID)
End Sub

Private Sub Worksheet_Deactivate()
Set mPasteSpecialButton = Nothing
End Sub
</Currently Worksheet code

NickHK

"Denys" wrote in message
oups.com...
Good day everyone,

Is it possible to force the user to paste on specific rows? As the
user will copy info from a page of a non Windows application and then
"Special paste Unicode text" on an excel spreadsheet, I'd like to
force him to paste only on specific rows, i.e. 1, 75, 150, 225, 300,
etc....

In other words, if there's something in cell A1, the you must paste on
cell A75, then the third time on cell A150 etc....

Is it possible?

Thanks for your time

Denys

Forcing to paste on a specific row
 

Maybe another idea...

Can you figure out where it should be pasted (in code)?

If you can, then maybe you could provide a dedicated worksheet where the user
always pastes in A1.

Then after they paste, you copy the range to the correct spot, and then clean up
that dedicated worksheet for the next time.

If the user doesn't need to make any other changes to the "real" worksheet, you
could even password protect it.

Then your code could unprotect it, do the work, and reprotect it.

Denys wrote:

Good day everyone,

Is it possible to force the user to paste on specific rows? As the
user will copy info from a page of a non Windows application and then
"Special paste Unicode text" on an excel spreadsheet, I'd like to
force him to paste only on specific rows, i.e. 1, 75, 150, 225, 300,
etc....

In other words, if there's something in cell A1, the you must paste on
cell A75, then the third time on cell A150 etc....

Is it possible?

Thanks for your time

Denys

--

Dave Peterson