How to get Independent Excel file/VBA code

[TheAceMan1]

Howdy All. Hope everyone is doing well . . .

Using Excel 2003 with Vista.

I copied an excel file [blue]AceTest.xls[/blue] to the [purple]same folder[/purple] and renamed to [blue]BBB.xls[/blue]. Then I moved the origional file [blue]AceTest.xls[/blue] to a different folder. Note that security is set to [purple]medium[/purple]. Upon opening [blue]BBB.xls[/blue] and attempting to run a macro via a toolbar button, I get the following popup:

[blue]'AceTest.xls' could not be found. Check the spelling of the file name, and verify that the file location is correct.

If you are trying to open the file from your list of most recently used files on the file menu, make sure that the file has not been renamed, moved or deleted.[/blue]

The macro simply opens a user form and now doesn't work/run.

I thought [blue]BBB.xls[/blue] and its code would be independent, so my questions are:
[ol][li]Whats the mechanism thats causing this?[/li]
[li]How do I make [blue]BBB.xls[/blue] and its macros/code independent?[/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

To All . . .

As a side note I've tested that the [blue]macros will work[/blue] if I run them from the [blue]macros dialog![/blue] So the error is tied to the toolbar button ...

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SkipVought]

What is the VBA code in BBB? This workbook may have some explicit name reference.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

I've encountered this.
Right-Click in the ToolBar area - Customise
Right-Click on the button your macro is attached to - Assign Macro
You'll see that the button is looking to the file in the old location.

To be fair to microsoft, when you copied AceTest.xls to a new folder how would Excel know that there were two copies or if it did know somehow that you had done this then how would it know which copy the button should point to? If you had opened AcetTest.xls and saved it to a different location with the name BBB.XLS then it might have had a chance!

One way of avoiding the issue is to link the toolbar to the workbook containing the macros. That way the button is always referring to a workbook that is open and does not store the file path. I have the menubar activate/deactivate when the containing workbook is opened / closed (using events). I think that this is an important part of the solution.

Gavin

 

[TheAceMan1]

How are ya Skip . . .

Thanks for getting back ... but I found the problem. Before I explain I'll answer your question first.

[blue][purple]What is the VBA code in BBB?[/purple] This workbook may have some explicit name reference.[/blue]

Its really simple [blue]Skip[/blue]. The toolbar button calls a macro named [blue]AddAce[/blue] ... which opens a user form named [blue]NewCurve[/blue]. The code of which is:

[blue]Sub AddAce()
   NewCurve.Show
End Sub[/blue]

Straight forward stuff.

I noticed the problem when I went to the [blue]Assign Macro Dialog[/blue] of the button (after 1st going to the [blue]Customize Dialog[/blue]). The assigned macro name was:
[blue]'C:\SpreadSheets\AceTest.xls' !AddAce[/blue]
Thats a long way from [blue]BBB.xls[/blue] ... and [blue]AceTest.xls[/blue] was moved.

While the macro listing revealed:
[blue]AddAce
C:\SpreadSheets\AceTest.xls AddAce[/blue]

It was a simple matter of re-assigning the macro name [blue]AddAce[/blue] that fixed the problem. On opening & reclosing [blue]BBB.xls.[/blue] the macro name became:
[blue]BBB.xls AddAce[/blue]

To circumvent this and not have to reassign for each copy, in the [blue]On_Open[/blue] event, I added the following lines:

[blue]   Dim cb As CommandBar

   Set cb = CommandBars("STS1")

   cb.Controls("Open Form").OnAction = "AddAce"

   Set cb = Nothing[/blue]

This assigns the proper macro name on opening of the workbook ... and no more problemo!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Gavona]

Sorry, just looked at the code. I Close the Commandbar using a Workbook_BeforeClose event
[Code}Private Sub Workbook_BeforeClose (cancel as boolean)
on error resume next
Application.commandbars("myMenu").delete
End sub[/code]

Gavin

 

[TheAceMan1]

Howdy Gavona . . .

Excel 2003.

Wether I save under a new name, attach the toobar, or copy and rename in explorer ... the copy always assigns with the path & name of the source file, with the macro name appended as in:
[blue]'C:\SpreadSheets\AceTest.xls' !AddAce[/blue]

This appears to be the [blue]default[/blue] assignment for any copy. As such, the copy can be moved anywhere but the source can't! Still ... its the dependency on the source thats undesirable. The code I gave in my prior post takes care of this nicely. Each copy aquires its own properly assigned macro references.

[blue]I have the menubar activate/deactivate when the containing workbook is opened / closed (using events). [purple]I think that this is an important part of the solution[/purple].[/blue]

Actually its a [blue]custom toolbar[/blue] I'm using (I should've mentioned this in my initial post). Menu & Toobars are global. If you leave them up they'll appear in any other workbook. So same as you I take care of this ... only using the [blue]On_Open[/blue] macro and the workbooks [blue]BeforeClose[/blue] event. I enable/show then hide/disable.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Gavona]

Yep, mine is a customtoolbar too.
You must remove the toolbar before you end the XL session. Otherwise it will be saved in Excel.XLB

Having attached the toolbar to the workbook containing the
code and closed that workbook, you must make sure that you don't have a toolbar of the same name in Excel before you close Excel.



Gavin