Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Save a macro in a new workbook (Excel xp)

    I have 2 macros in an excel workbook called main.xls. One macro adds a new workbook and copies one of the sheets from main.xls into the new workbook. Both the macros are on a custom menu item in main.xls.

    My problem is that when i save the new workbook, i want to have the new menu item come along with the new workbook but only have 1 of the macros appear. How can I accomplish this? Thank you for your help.

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

    Re: Save a macro in a new workbook (Excel xp)

    Why add the macro to the new workbook? I would try and keep macro's separate from the rest, thus easing the process of updating your macro's (with your method you'll end up with many copies of your macro, which are almost impossible to update (if ever necessary).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Save a macro in a new workbook (Excel xp)

    I wanted the user to have access to the macro in the new workbook. How do i distribute the macro to the new user that doesn't have it? thanks

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

    Re: Save a macro in a new workbook (Excel xp)

    What you do is:

    - Create a new workbook that contains your macros (make sure those macros refer to the objects in the right workbook -e.g. the Activeworkbook- when doing anything).
    - Then (if appliccable) attach toolbars to it (see my boilerplate advice on them below).
    - Save this new workbook.
    - Hide it (Window, Hide)
    - Close Excel and say Yes to save changes to this workbook.

    Now just send a copy of this new workbook to all your users. You may have to specify (depending on your code) what directory they should put it in.

    Toolbars:

    You can attach a toolbar to a workbook. When this workbook is loaded, XL checks if the toolbar is on the system. If not, it copies the toolbar from the workbook to the system.

    After creating *or changing* the toolbar, you should attach the toolbar to your workbook:

    - activate the workbook to which you want to attach the toolbar
    - Rightclick the toolbar, select 'customize'
    - Click 'Attach' (Toolbars Tab)
    - If the workbook already contains a toolbar by that name, delete it first by clicking on it on the righthand side and choosing Delete.
    - Select your toolbar (on the left) and press 'copy'
    - Save the workbook (optionally: save_as an add-in).

    Also, You should include code that deletes the toolbar when your workbook or add-in is closed, so that when you deliver a new version of your workbook the new toolbar will be used i.s.o the old one. You can do that in the Thisworkbook module, using the Workbook_beforeClose event:

    Private Sub Workbook_beforeClose()
    On Error Resume Next 'In case Toolbar is absent
    Application.CommandBars("YourBarsName").Delete
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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