Results 1 to 10 of 10

Thread: Assigning VBA Sub-routines to Buttons

  1. #1

    Assigning VBA Sub-routines to Buttons



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi, I have written several Sub-routines to manage and manipulate a series of spreadsheets in one file. I am trying to make it easy to call these sub-routines using either a custom made menu or assigning each sub-routine to a button. Each time I insert the button into the spreadsheet, the application wants to create a new macro. Obviously I am missing something.

    Is it possible to assign VBA to buttons? Will someone tell me how?

  2. #2
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    183
    Articles
    0
    Excel Version
    2010
    When you create a button you can assign an existing macro using the dialog that pops up. You can also right-click an existing button and choose Assign Macro...
    Circumference of a circle = 2πrē



    ēthe circle's radius

  3. #3
    Yes, thank you for your reply but something is up with the way my code is written or saved. I just jumped right in and wrote several subroutines to compare and copy information from several different sheets. I did not record any macros. In any event, none of my subroutines appear when I try to "assign macro" to a button. When I click the edit button a new macro is created....no choices...mmm.

    Is this assign button feature only for macros or can you assign VBA subroutines to buttons also? Do these subroutines need to be made public or global....not sure never really programmed in VBA or Excel before. Thank you in advance for your help.

  4. #4
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    183
    Articles
    0
    Excel Version
    2010
    Macros are VBA subroutines. They need to be in a normal module (not Worksheet/ThisWorkbook) and not be Private (Public is the default).
    Circumference of a circle = 2πrē



    ēthe circle's radius

  5. #5
    First, Thank you, Making the subs "Public" worked. I really appreciate your help. I want to set this up right so it can grow correctly if need be.

    Should all my subs be in a separate module? They are in one "General" module called [Module6 (Code)]. 5 subs are in this module.

    Also, I noticed they are all private...not sure how this happened since public is the default.

  6. #6
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    183
    Articles
    0
    Excel Version
    2010
    No they don't need to be in separate modules. I tend to group my routines into modules based on what they do but it's purely personal preference. There is a size limit of 64KB for a module but that's quite a lot of code.
    Circumference of a circle = 2πrē



    ēthe circle's radius

  7. #7
    Agree! 64K of pure text..... something needs to change.

    My goal here to to create a "main menu" to make it easier for people to execute the routines. In your opinion, is it best to do this in one of the sheets of the file or create the menu from scratch with code? I can get up an going really fast by assigning routines to buttons; However, I am not sure how that will work in the end when I start to require feedback from the user.

    Honestly it has been kind of hard to find main menu or switch board examples when searching the forum resources. I am sure it is because I do not know where to look. Is there a library for example resources?

    Thank you

  8. #8
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    183
    Articles
    0
    Excel Version
    2010
    I would create a menu, personally. Which version(s) of Excel are you dealing with?
    Circumference of a circle = 2πrē



    ēthe circle's radius

  9. #9
    version 10

  10. #10
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    183
    Articles
    0
    Excel Version
    2010
    Do you mean 2010 or XP (which was version 10)?
    Circumference of a circle = 2πrē



    ēthe circle's radius

Posting Permissions

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