Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Assign Macro

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    67
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is it possible to assign one macro to a button in toolbar to run different workbooks??? So each workbook can run the macro independently and not to rely on the other workbooks.
    I copy the macro's VBA code to each workbook. However, when I run the macro, it still open the workbook which the macro is last assigned by.
    What should I do??? Please HELP

    Thanks in advance

  2. #2
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    816
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Allen,

    There is a file called as Personal.xls. It is personal workbook and it opened everytime you open a workbook, and it is hidden as default. You can store your macros in it so you dont need to copy macros in all workbooks that you want to use in. To create a personal.xls if you have not already just Tools_Macro_RecordNewMacro and select Personal Macro Workbook for "store macro in" choice. After recording a simple macro by this way you can goto to VBE and see the Personal.xls file. Just move your macros there.

    Thats it.

    Regards


    Suat

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    67
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Where is this Personal.XLS file saved?
    After I copy the macro to the Personal.XLS, do I still need the VBA code that I copy in each workbook??
    Thanks in advance

  4. #4
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    816
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    It should be saved in C:WINDOWSApplication DataMicrosoftExcelXLSTART when you first create a macro in Personal Macro Book but you dont need to find that file because it would be opened automaticially. Just record a macro by selecting Store Macro In Personal Macro Book. Then goto VBE. You will see what i meant.

    Then no, you dont need to copy all common macros anymore.

    Regards
    Suat

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hiya,

    It really depends on your set-up, but it's likely to be something like:
    C:program filesmicrosoft officeofficexlstart
    or similar.
    The beauty of the personal.xls is that it loads it everytime you start Excel (like a normal.dot in Word).

    So you can just keep stuff in there and not worry about copying code.

    Rgds
    AJ

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    67
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If my company need to distribut all the workbooks to the other companies by e-mail. Do I need to copy that Personal.xls file to make the macro run?
    Thanks in advance

  7. #7
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    816
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Oh no,

    I mean yes it is possible but not right way i think, distributing your personal.xls, because their personal.xls files would be destroyed and/or you maynot want to distribute all your personal macros. You should better write a macro which transfers these macros into the document which would be shared when you want to send them. Or if it is not problem that all personal macro books are same then you can send also personal.xls


    Suat

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Posts
    67
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If I only have one macro in Personal.xls. Is it a good idea to just send the Personal.xls???
    When the user receive the Personal.xls, does it have to be put into the XLSTART folder to work, or doesn't matter where the user put it??
    Thanks in advance

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    this would be fustrating for the end users, to have to go and save a file to that specific file location, unless you want to distribute it so that everyone has the "always on the toolbar" button, in which case you would have to issue instructions or put a macro in the personal.xls to set this up for your users. if you're going to do this might as well put some code in the personal.xls to go save it to the write location, just make sure you cover your bases as different operating systems or versions of excel probably use different folders for this.

    if it just for use in the workbook, then i would put the code in the workbook and put a button on the sheet or something like that. I have personally never needed to distribute a personal.xls, except maybe to help out the guy sitting beside me, for general distribution i would always build a self contained workbook, with menu page and buttons on that page.

Some videos you may like

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
  •