Results 1 to 11 of 11
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Multiple Macros In One Spreadsheet (2000)

    How do I put several macros into my default spreadsheet that will always be there when I first initialize (open) Excel?
    Thanks.

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Macros In One Spreadsheet (2000)

    Save your file in C:Program FilesMicrosoft OfficeOfficeXLStart and make sure this corresponds to Tools:Options:Generalefault File Location.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Multiple Macros In One Spreadsheet (2000)

    How can I make sure it corresponds?

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Macros In One Spreadsheet (2000)

    Tell you what: ignore that bit about Default File Location: my brain is down for the weekend. It should work if you just dump the file in XLStart

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Multiple Macros In One Spreadsheet (2000)

    Thanks, and where do I put the macros?

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Macros In One Spreadsheet (2000)

    You should put those macros in your Personal.xls file. If you select Macro from the Tools menu and then select Record New Macro and then in the dialog box select Personal Macro Workbook in the box labeled Store Macro In, and then select a cell on the worksheet and stop resording, Excel will create a Personal.xls workbook for you. You can then search your hard drive for this workbook. This is the place you should put macros that you always want available. You can put your real macros there and delete the one you just recorded.
    Legare Coleman

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Macros In One Spreadsheet (2000)

    Brooke: The macros should be put into the Personal.xls workbook which Excel treats a bit differently. See my other post.
    Legare Coleman

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Multiple Macros In One Spreadsheet (2000)

    And this Personal.xls workbook will open up each time I start Excel?

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Macros In One Spreadsheet (2000)

    Yes, that is what it is for. Excel will put it into your XLStart directory and all files in that directory are opened every time you start Excel.
    Legare Coleman

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Multiple Macros In One Spreadsheet (2000)

    Thanks, Legare.

  11. #11
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Macros In One Spreadsheet (2000)

    Granted. I've just never used Personal.xls. Actually, what I do is have a file in C:Program FilesBrooke which has an attached custom menu, one of the buttons on which calls a macro as below:

    Sub DeliberatelyBlank()
    End Sub

    the custom menu is always available, so if I want my tools file open, I hit the button and it runs DeliberatelyBlank and because this opens my tools file, the Workbook_Open code is fired, which builds a submenu just before Help on the Worksheet Menu Bar - amoung others. Convoluted I know, but it works for me. This way I can turn my customisations on and off easily. And why have I never used personal.xls? Not sure really - I seem to recall somebody once telling me that this way lay problems akin to modifying Normal.dot in word, so I just never went there.

Posting Permissions

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