vba code to assign macros
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: vba code to assign macros

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    New Zealand
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am having trouble re-assigning macros to buttons(shapes?) using vba.
    I have tried using the following:
    ActiveSheet.Shapes("Button 3").Select
    Selection.OnAction = "Data_1"
    ActiveSheet.Shapes("Button 1").Select
    Selection.OnAction = "Data_2"

    I created these buttons from the forms toolbar in excel.
    Some sheets have 6 or more buttons.
    This works fine on the original worksheet but when I copy that sheet to another book the names "Button 3 change e.g button 3 might become button 5.
    Is there any method I can use in vba to do this.

    Thanks

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi John

    Not to sure I follow but try this

    With Sheet1
    .Shapes("Button 1").OnAction = "MyMacro1"
    .Shapes("Button 2").OnAction = "MyMacro2"
    End With

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    New Zealand
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Dave

    The problem was not in getting the macro to run but rather in that the button names changed. e.g on one sheet when I right click on the button the name in the top left command bar shows as button 22. When I copy this sheet the name changes to button 4. After playing with this sheet I discovered that every time I copied it it kept the button 4 name, so I have changed my macro accordingly. Interestingly not all buttons on a page change name.

    Regards
    John

  4. #4
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-01 21:00, John wrote:
    Thanks Dave

    The problem was not in getting the macro to run but rather in that the button names changed. e.g on one sheet when I right click on the button the name in the top left command bar shows as button 22. When I copy this sheet the name changes to button 4. After playing with this sheet I discovered that every time I copied it it kept the button 4 name, so I have changed my macro accordingly. Interestingly not all buttons on a page change name.

    Regards
    John
    Hi John, Explicitly nameing your buttons
    will take care of the copying.
    eg Right click then select the Name dropdown
    list to the left of the formula bar.

    Type in a meaningful name eg

    btn_Format , btn_Clear etc.

    Copying these buttons will retain there name



    Kind Regards,
    Ivan F Moala From the City of Sails

  5. #5
    New Member
    Join Date
    Mar 2002
    Location
    New Zealand
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Ivan

    I had tried naming buttons this way but they had always reverted to the original name. I have tried again using your suggestion and it works fine. On reflection it may have been I was trying to give it a new button number rather than a name and it is possible a button with the same number existed on the worksheet.

    Thanks for the useful and helpful advise

    Regards
    John

  6. #6
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi John

    Just realised....another Kiwi
    Haven't seen many NZ'ers @ this Board
    Welcome

    _________________
    Kind Regards,
    Ivan F Moala
    http://<font color="green"><a href="...r Construction

    [ This Message was edited by: Ivan F Moala on 2002-04-02 21:32 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

DMCA.com