Results 1 to 3 of 3
  1. #1
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    VBA creating button and connecting a macro (2003)

    Hallo all,

    I need your help here.
    I have to create a button in 270 Excelbooks that will start a macro.
    The macro is already in de the workbooks.
    So what I have done is created an update macro in a new workbook. This macro will open one-by-one the 270 workbooks add the button to the correct sheet and close the workbook:

    ActiveSheet.Buttons.Add(665.25, 5.25, 244.5, 69).Select
    Selection.OnAction = "prcShowWW"
    Selection.Characters.Text = "button text"

    the problem is that after the update, I open one of the updated workbooks and press the button an error pops up, telling me that de macro could not be found.
    Looking at the button settings I see that the macro name is correct but is refrerring to the update workbook (which doesn't have that macro)
    So this means that when creating a button in workbook (A) by code from workbook ([img]/forums/images/smilies/cool.gif[/img] generates a macrolink in the button to workbook ([img]/forums/images/smilies/cool.gif[/img] and that should not be the case.

    Do you have any idea how to solve this... (other than doing all the adding manualy)

    greetings,

    Patrick Schouten
    Meander Medisch Centrum
    The Netherlands
    Greetings,

    Patrick Schouten
    (The Netherlands)

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA creating button and connecting a macro (2003)

    Like this:

    Selection.OnAction = "'" & Activeworkbook.name & "'!prcShowWW"
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: VBA creating button and connecting a macro (2003)

    Thanks Jan Karel,

    You saved me a lot of time.

    greetings,

    Patrick Schouten
    Meander Medisch Centrum
    Greetings,

    Patrick Schouten
    (The Netherlands)

Posting Permissions

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