Consulting

Results 1 to 6 of 6

Thread: Unbuttoned

  1. #1
    VBAX Newbie
    Joined
    Jun 2004
    Posts
    2
    Location

    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

  2. #2
    VBAX Contributor Richie(UK)'s Avatar
    Joined
    May 2004
    Location
    UK
    Posts
    188
    Location
    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.

  3. #3
    VBAX Newbie
    Joined
    Jun 2004
    Posts
    2
    Location
    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.

  4. #4
    VBAX Contributor Richie(UK)'s Avatar
    Joined
    May 2004
    Location
    UK
    Posts
    188
    Location
    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.

    [vba]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[/vba]

    HTH - if not, let me know and we'll see what can be done.

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

  6. #6
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,892
    Location
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •