Solved: Paste Special problem in found solution

[cfitzsimmons]

I have been working on solvinga Paste Special... Value need for some programming in Excel 2007 and I ran across a great solution.
'Written by Aaron Bush 08/06/2007
'Free for private Use, provided "As-Is" with no warranties express or implied.
'Please retain this notice.
The only problem is this section and others like it do not work
On Error GoTo Err_Hnd
Dim oPasteBtns As Office.CommandBarControls
Dim oPasteBtn As Office.CommandBarButton
Dim oNewBtn As Office.CommandBarButton
Const lIDPaste_c As Long = 22
RestorePasteButtons
Set oPasteBtns = Excel.Application.CommandBars.FindControls(ID:=lIDPaste_c)
For Each oPasteBtn In oPasteBtns
Set oNewBtn = oPasteBtn.Parent.Controls.Add(msoControlButton, Before:=oPasteBtn.Index, Temporary:=True)
oNewBtn.FaceId = lIDPaste_c
oNewBtn.Caption = oPasteBtn.Caption
oNewBtn.TooltipText = oPasteBtn.TooltipText
oNewBtn.Style = oPasteBtn.Style
oNewBtn.BeginGroup = oPasteBtn.BeginGroup
oNewBtn.Tag = m_sTag_c
oNewBtn.OnAction = m_sPasteProcedure_c
oPasteBtn.Visible = False
Next
Exit Sub
Err_Hnd:
VBA.MsgBox VBA.Err.Description, m_lButtons_c, m_sTitle_c & CStr(VBA.Err.Number), VBA.Err.HelpFile, VBA.Err.HelpContext
End Sub

This part seems to be the problem
Set oPasteBtns = Excel.Application.CommandBars.FindControls(ID:=lIDPaste_c)

Complier Error: Method or data member not found

This is pointing to 'FindControls' as the problem. If I look forList Properties 'FindControls' does not exist and 'Find Control' does not work for this need

Anyone have any suggestions?

Thanks ahead of time.

Carl

[Kenneth Hobs]

Welcome to the forum!

It is best to just post the link rather that reposting kb code. http://www.vbaexpress.com/kb/getarticle.php?kb_id=957

You will need 5 posts before you can do that though. Just say kb article 957 in that case.

I guess the good news is that the code works fine in 2010. I don't have 2007 or I would test it for you. Be sure to add the ThisWorkbook code as detailed in the kb and then close and open the workbook.

Private Sub Workbook_Activate()
ForcePasteSpecial
End Sub

Private Sub Workbook_Deactivate()
ReleasePasteControl
End Sub

[cfitzsimmons]

Got it in there - thanks

I am almost there to a Paste Special solution. I made an intercept for 'cntl v' to a VBA code piece. I am grabbing the data from the clipboard. However if the size is not the same i.e. 1 cell to 1 cell the paste special fails. Recording a macro does no good because this also fails in VBA for same reason even though you can do it using Menu driven Paste Special Value

So I am looking for a way to parse the text on clipboard so when I select a single cell I want to start the Paste Special that the many rows and columns from the clipboard contents are Paste Special Value into the spreadsheet and perform paste special value into the correct rows and columns.

I thought this would be simple since you can do this using the menu or control right mouse button but it appears to be a bit more difficult.

Any suggestions

[cfitzsimmons]

In case you are interested a solution exists here mrexcel.com/forum/showthread.php?t=56674 by Jaafar about 2/3 the way down

[Aussiebear]

In case you are interested a solution exists here mrexcel.com/forum/showthread.php?t=56674 by Jaafar about 2/3 the way down

Care to quote the solution here since you raised the issue here?

[cfitzsimmons]

Here is a solution that I found by Jaafar Tribak

Place the first code below in the WorkSheet Module and the Second one in a Standard Module :


1st Part Of Code:
Private Sub Worksheet_Activate()
Customise_CutCopyPaste
End Sub

Private Sub Worksheet_Deactivate()
Restore_CutCopyPaste
End Sub

Public Sub Customise_CutCopyPaste()
With Application
.OnKey "^x", "CutSource"
.OnKey "^v", "PasteTarget"
End With

With CommandBars("Edit")
.Controls("Paste").OnAction = "PasteTarget"
.Controls("Paste Special...").OnAction = "PasteTarget"
.Controls("Cut").OnAction = "CutSource"
End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Customise_CutCopyPaste
End Sub

Public Sub Restore_CutCopyPaste()
With Application
.OnKey "^x"
.OnKey "^v"
End With

With CommandBars("Edit")
.Controls("Paste").OnAction = ""
.Controls("Paste Special...").OnAction = ""
.Controls("Cut").OnAction = ""
End With
End Sub

2nd Part Of Code:
Public CutMode As Boolean
Public SourceRange As Range

Public Sub CutSource()
CutMode = True
ActiveCell.Copy
Set SourceRange = ActiveCell
End Sub

Public Sub PasteTarget()
If Application.CutCopyMode = xlCopy And CutMode = True Then
ActiveCell.PasteSpecial xlPasteValues
SourceRange.Clear
CutMode = False
Else
ActiveCell.PasteSpecial xlPasteValues
End If
End Sub