Unbuttoned

I have a VBA module in excel which is accessed via a button on the page. I am programatically copying this page and the code module to a new workbook. Everything is fine EXCEPT when I click the button, it tries to access the macro in the original book, not in the cloned book. I know how to change it manually, BUT I will be distributing this function to others, so I must be able to change it programatically when I clone the worksheet.

Function:
User opens file and envokes main macro
Main macro creates new workbook and clones worksheet
Main macro copies aux macro into new workbook
Main macro populates new workbook with data
Main macro exits

User is now left with a valid XLS workbook ... the only problem is that the button still points to original workbook.

Buz

Hi Buz,

I'm betting that the button you use is taken from the Forms toolbar, right? Try using a button from the Controls Toolbox toolbar instead, from memory that should do the trick.

If not, you will need to look at amending the OnAction details of the button that is copied so that the workbook names are correct. Let me know if the first suggestion doesn't work and I'll put some code together for the second suggestion. ;)

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.

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.

Quote:

---

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 ;)

Quote:

---

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.

Code:

---

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.

If you can clone it, why not do so and change the button in your copy and then distribute your copy?
ttfn
Kicker

I see that Richie helped you out on this one. I had the same thing happen. The OnAction property is probably the easiest way to go, if you don't want to change anything else.

Quote:

---

Originally Posted by Richie(UK)

... you will need to look at amending the OnAction details of the button ...

---

Did you get this resolved, btw?