macros not staying with spreadsheet

[jbrown2339]

I've got a series of reports in Excel, that I feed a bunch of data into, and give to the end users. Basically, I have a file called "Trend Report - Template.xls", and as I feed data into it from each of our markets, I save under different names, i.e. Dallas Trend Report.xls, Houston Trend Report.xls, etc.

To make navigation in this spreadsheet easier for the end user, I created some simple buttons where they could hit it, and be taken to a specific sheet. I recorded each macro, and selected to store the macro in "This Workbook". When I do that, it's fine. Once I change the name of the spreadsheet, then the macros' no longer work. For instance, if I changed the name of the spreadsheet to 'a.xls', then I get an error, as it says it can't find the 'Trend Report Template.xls' spreadsheet, even though the macro only specifies a worksheet name, and not the full name of the actual excel file.

Any idea why the macro's are still looking back to the original file, vs only looking internally in the file I have open? Since I am creating these on my hard drive, and exporting to various users across the country, their reports won't be able to look back to my original template file on my C drive.

 

[SkipVought]

Hi,

Are your macros stored in Trend Report - Template.xls or are they stored in Personal.xls?

Do you do a SaveAs from Trend Report - Template.xls to name Dallas Trend Report.xls or are you COPYing the sheet tab to a new workbook and doing a SaveAs?

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[jbrown2339]

When I record them, I instruct Excel to record them in "This Workbook". So my intention is to have them saved in 'Trend Report - Template.xls'.

I am doing a SaveAs to create the "Dallas Trend Report.xls", and then I either email or place the new files out onto a server (some of my users have access to the servers, others do not). If I build the macro onto a spreadsheet on a server everyone has access to, then when they hit these 'navigation' buttons, they work. However, if I create the macros in a file on my hard drive and then others try to use the reports, rather than be taken to the appropriate page, they get an hourglass and it hangs there forever, or else Excel tells them it cannot find 'Trend Report - Template.xls". So these macros aren't really existing in the workbook, they are still going back to the original file. If I rename the original file, to "test.xls", the same thing happens. It errors out trying to find "Trend Report - Template.xls", even though I'm working in the exact same excel file just renamed.

I've got several other buttons in the excel program, and they all work just fine. But these are control buttons embedded on the actual page, not in a toolbar. Since there are about 50 pages in this spreadsheet (large set of reports), I wanted a few buttons on the toolbar, so the user can easily get around in the reports.

 

[SkipVought]

However, if I create the macros in a file on my hard drive and then others try to use the reports, rather than be taken to the appropriate page, they get an hourglass and it hangs there forever, or else Excel tells them it cannot find 'Trend Report - Template.xls".

How are others getting to your hard drive???

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[jbrown2339]

Sorry if this is confusing. Maybe this will help:

File A : Resides on a public server. If I record macros to a button on this file, and then email the file to a coworker, they can run the buttons. If I email the file to a person without access to this drive, once they hit a button either their machine freezes with an hourglass, or else they get an error that excel can't find 'File A'.

File B: Resides on my hard drive. If I record macros to a button on this file, it works fine for me. Others get either a machine with a perpetual hourglass, or an error message that Excel can't find 'File B'.

In both cases, I create the macros and specify to save them to 'this workbook'. That's why I think the macros, even though I select them to be saved in the workbook, are still going back to the original file for whatever reason, as other people can only use those buttons if the original file I created resides in a public space, vs my hard drive.

 

[SkipVought]

Where is 'File B'? Do you have a reference to another workbook in your code?

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[jbrown2339]

Those are just examples. Let me explain again. Sorry if I've been confusing. I am recording a simple macro on an excel file with about 50 sheets. So I have created a navigation toolbar, with two buttons on it (which I attach to a macro), to take the user to the main 'sheets' in this report. To record the macros, I am doing the record macro feature. When I start recording, I select the sheet I want the button to go to, and hit the stop button. If I look at the code, it only references a worksheet, not a path to the actual file I am working in.

Once I do this, the buttons work just great. As long as I don't move the file from the location I created it, everything works. If I change the name of the file, the buttons error out, and it asks me where the .xls file is with the original name. If I send the file to another coworker who doesn't have access to the drive the excel file was created in (whether on my hard drive, or on a network drive), the buttons won't work. If the coworker has access to the drive that the original file is sitting in, the buttons do work. If I make a copy of the original file and store it in a place I have access to, the buttons work. If I make a copy of the original file, then change the name of the original file, the copied file will no longer work (again, it asks where the original file went).

Why is this happening? The macro has no reference to a fixed location. It just says to go to 'data selections' or 'navigation', which are in every spreadsheet of this series. However, if the original file where I created the macro is missing, renamed, or in a location where the user has no access to it, the macro's won't work. And I've made sure there is no code in those macro's with any kind of path or file location. It should only look within the file itself, but it's not. It's obviously trying to find the original file where the macro was recorded.

 

[GlennUK]

The problem isn't your macro, it's the toolbar buttons that are pointing to the wrong location.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[jbrown2339]

Oh, didn't think about that. I did the Tools->Customize->Toolbars->Attach command. Is there another step I should take?

Thanks, I never even thought of that...

 

[GlennUK]

I'm afraid that trying to distribute toolbars attached to workbooks is not a good idea ... have a look at this discussion: thread707-1061166

in which combo says

Toolbars in workbook are not easy to handle, when you add a toolbar to workbook and open it, it starts its own life, sticks to excelXX.xlb file. It also points to macro location valid when created. When a workbook is saved as an add-in, it usually is stored in another location, moreover, it can't be changed. You do not also have programmatic access to collection of toolbars within the workbook.
So you can either use Workbook_Open event to manage toolbars (and assign proper macro file), or, what is frequently used, dynamically create and destroy the toolbar using Woorkbook_Open/Close (or Workbook_AddinInstall, more risky) event handlers.

combo

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[jbrown2339]

Thanks so much, I've been cracking my head trying to figure out why the buttons were doing that, and now it totally makes sense! It was the toolbar and not the buttons. Thanks so much for your help!! I was totally thinking in the wrong direction.