cfitzsimmons
03-15-2012, 06:46 PM
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
'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