Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cut and paste specific data | Excel Discussion (Misc queries) | |||
Copy, Paste in a specific format | Excel Discussion (Misc queries) | |||
Forcing scientific notation to a specific power | Excel Discussion (Misc queries) | |||
Forcing a specific date format | New Users to Excel | |||
Macro to Paste to specific line, and continue to Paste each time on next row not over | Excel Programming |