Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Dec 2003
    Location
    Illinois, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem w/ macro assigned to custom toolbar button (Excel 2000)

    Problem with macro assigned to custom toolbar button

    I am having a probelm with macros assigned to a custom button on a custom button. I am using excel 2000.

    1. I have created a macro called "Jump" in a workbook named "Test1"

    2. I then created a custom toolbar and added a custom toolbar button to that toolbar.

    3. Through the customize dialog box I assign the Macro "Jump" to the custom button.

    Everything works fine UNTIL.

    If I do a SAVE AS for the workbook "Test1" and call it something else like "Test2" and then close "Test2" and reopen "Test1" the name of the macro assigned to the custom button has change

    Where as before it was simply "Jump"
    now it is C:MydocumentsTest2.xls'!Jump.
    Since I have the workbook "Test1" open and "Test2" is closed excel now wants to open up "Test2" to access the macro assigned to that button.

    I am creating a custom toolbar that will go with a excel sheet that I use as a template and will often resave it with different names.

    I would like excel not to go looking in other workbooks for these macros since copies of the macros exist in the module attched to the workbook

    Every time I do a Save As the assigned macro gets renamed

    Does anyone have any ideas

    Spydergt1

  2. #2
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem w/ macro assigned to custom toolbar button (Excel 2000)

    After creating your toolbar button and macro, try doing a SAVE then SAVE AS.
    Without the first SAVE, your original Test1 file will not be saved, hence will not have your latest modifications.
    If you do a SAVE, then SAVE AS both files should contain your macro.
    HTH

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Problem w/ macro assigned to custom toolbar button (Excel 2000)

    The "problem" you experience, is, as far as I know, a feature that excel has to make your life easier. I know many of the "features" are not really but this one probably works the way most people would want it and so might require you to change.

    If I understand, you have a macro named Jump in a workbook Test1
    You create a toolbar button and assign the button to this macro.

    This is as expected.
    If you do a SaveAS, excel realizes that the button will no longer be assigned to an open file, and figures that since it is assigned to Jump in Test1 and Jump in Test1 is becoming Jump in Test2, that it will be "helpful" and fix the link for you.

    This is not what you want.

    Why are you saving As this file. You are putting the JUMP routine in all subsequent copies of the workbook which is (obviously) unneeded since you want it to run from test1. Why not put the JUMP macro in your personal.xls file and have the button assigned to that workbook? Then it will not be reassigned and you will not have multiple copies of JUMP in all the copies: There will be only 1 copy of it.

    Also If you are doing SaveAs tot the file, you might consider creating a template of Test1, then you just have to file-New and choose the template to get a copy of this file without even touching the other one. (this in itself might solve the problem of the reassigning, but it still creates the multiple copies).

    Steve

  4. #4
    New Lounger
    Join Date
    Dec 2003
    Location
    Illinois, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem w/ macro assigned to custom toolbar button (Excel 2000)

    Steve

    Thanks for looking at this, to give you a little more background let me explain

    The excel file is a template that my company uses for construction cost estimating. The workbook contains about ten sheets that all flow from on to another. This first sheet is very long. Something like 2000 rows. It is broken up in to section such a concrete, masonry, steel, .....

    The reason I created the tool bar was to provide people with a fast way to jump from one section of the first sheet to another instead of scrolling. This toolbar is only needed on sheet 1 and not on the others so I have written some simple VBA using the active and deactive events to make it pop up and close. It also closes the toolbar when you close the workbook since you do not need this toolbar for other spread sheets.

    Now I need to get a little in to my office culture. Their are about 20 different people who do construction estimating at my company. Each time we do a new estimate we start with our master file and the do a SAVE AS. We rename the file with a name that is associated with the particular construction project. Depending on the project we may estimate it 2 or 3 different ways and may have 2 or 3 versions of this file.

    In general most of the people in my office have pretty basic knowlege of excel. They can use basic mathmatic formulas, format cells and worksheets, maybe record a macro or two if they are more advanced. I doubt anyone else has a clue about VBA and I only know a very little.

    In short for this jumping toolbar to be useful to my company it needs to be idiot proof.

    I dred the idea of going to everyone's computer and helping them with their personel.xls file. Aside from that, many people take copies of these files home to work or access them through citrix from remote locations.

    I am not opposed to achiving my goal a different way. The nice thing about a toolbar vs. a form on the worksheet is that it is always at the top and is independent of how you scroll through the worksheet.

    So what do you think

    Dan

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Problem w/ macro assigned to custom toolbar button (Excel 2000)

    You can attach the toolbar to the workbook, and delete it when the workbook is closed:

    1. With the workbook open, select Tools | Customize...
    2. Click Attach...
    3. Select your custom toolbar, then click Copy.
    4. Click OK, then Close.
    5. Put the following macro in the ThisWorkbook module, with the appropriate name substituted for YourToolbarName

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.CommandBars("YourToolbarName").Delete
    End Sub

    6. Save the workbook.

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem w/ macro assigned to custom toolbar button (Excel 2000)

    You would eliminate this problem (I think) if you created/ destroyed the commandbar each time the workbook is opened/ closed. This can be done with Auto_Open() and Auto_Close() macros. To build the commandbar, you can use a separate procedure, as outlined under "CommandBar Object" in the help file. I could dig out a working example if you like, but I'm not very good at coding off the top of my head.

    Alan

Posting Permissions

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