Originally Posted by
Buz
The problem is that I am not the one who created the original sheet, so I am not authorized to make changes to it. I can clone it, and massage the data in it ... but I can not change the button.
If you can 'clone it' why can't you change the button on a copy of the original? I don't see the problem, but hey it's your project
Originally Posted by
Buz
Where do I find the "OnAction" property? I have looked, and I cannot find the button as an object in the workbook. If I could find it, I could change it.
OK, the following code will copy the activesheet - the one with the button and in the original workbook - and display the details on the OnAction property (the macro that it will run). It will then save the newly created workbook and amend the OnAction property so that it 'points' at the new workbook.
Sub ButtonSwap()
Dim strOA As String, wbkNew As Workbook
ActiveSheet.Copy
Set wbkNew = ActiveWorkbook
wbkNew.SaveAs Filename:="TestBook.xls"
strOA = ActiveSheet.Buttons(1).OnAction
MsgBox "The old OnAction was : " & strOA
ActiveSheet.Buttons(1).OnAction = _
wbkNew.Name & "!" & Right(strOA, Len(strOA) - InStr(strOA, "!"))
strOA = ActiveSheet.Buttons(1).OnAction
wbkNew.Save
MsgBox "The new OnAction is : " & strOA
End Sub
HTH - if not, let me know and we'll see what can be done.