Can't run macros off a custom toolbar anymore!

[globulous]

A couple years ago i made a giant spreadsheet and had 2 custom toolbars to run macros from to manipulate the sheet.

This year i re-did the sheet, meaning all my old data was replaced (it's a giant schedule basically).

However, the Macros in the custom toolbars stayed exactly the same and performed the same function and are directed to the exact same cells, rows, columns, etc..

But now when i hit the buttons on the custom toolbars to run my macros, i get the following message... all the time...

**
A document with the name "NHLsheet.xls" is already open. You cannot open two documents
with the same name, even if the documents are in different folders.
To open the second document, either close the document that's currently open, or rename one of the documents.
**

Trouble is, i have only 1 document open!
There's something in the macros that thinks i'm trying to open a new document but i'm clueless as to where or why.. they are very simple macros
that do not require the opening of any new workbooks..

All they do is point to another sheet within my open workbook, "NHLsheet", copy a particular row from that sheet, and paste it to a specific spot onto another sheet within my workbook, "NHLsheet".


Here is a sample:

Please Login or Register  to view this content.

I think it has to do with the fact that i created these macro toolbars 2 years ago for use in the workbook i had used 2 years ago..

though nothin has really changed..

I can't figure out why it insists that there is another copy of "NHLsheet" open somewhere??

Can anyone help?

thanks,

[Spudley_Jones]

What version of Excel are you running versus the version you code in?

[globulous]

I use Excel 2000 and the VBA that came with it.. MS VBA 6.0

I used Excel 2000 a few years ago too.. and VBA 6.0 to create my old sheet (which worked perfectly and never had this problem), however it was on another computer..

i copied the old .xls file onto my new comp, along with re-installing Office 2000 (with Excel) and updated everything after i made the changeover

[Spudley_Jones]

Your code looks sound. What about the code for the custom menu?

[globulous]

well that is the custom menu..

basically it's set up as to menu boxes..

Away and Home


Under away it has all 30 NHL teams laid out as buttons from which macros run..
exact same on the home side..

so for instance, when i click ANA in the HOME box, the above Macro runs..

When i click, say COL in the AWAY box, then the following code runs...

Please Login or Register  to view this content.

[Spudley_Jones]

I took a closer look and saw with this code:

Please Login or Register  to view this content.

you are selecting a sheet and then a row and copying
then you are selecting another sheet and row and the pasting on the active sheet....not the selected sheet..
is this supposed to happen?

[Spudley_Jones]

Please Login or Register  to view this content.

see if this code gives the same error

[globulous]

yeah..
the macro buttons select a specific row from 3 different sheets (offence, defence and special teams) and then pastes each of those rows to specific spots on the sheet, 'tester'..

i dont think it matters whether or not any of those sheets are active because the macro is set up to activate it on its own isn't it?..

like i say, the confusing thing here is that basically NOTHING changed in the code..
in fact about a month ago when i started re-entering the schedule data, the code was working fine..

i'm stumped as to why it's not now....


How about this.....


If i go into the macro menu where you can Run/Edit/Debug, etc.. and i click on, say, "ANAhome".. the originally posted code... and click "RUN"... it works perfectly..

it doesn't give me this "file already open" BS.... that only happens when i run it from the menu buttons.

does that help anymore?

[globulous]

i tried that code and still the same result

[Spudley_Jones]

yeah, is there a way to select which macro the menu button runs?

[globulous]

i was able to do that when i set up the toolbar (menu buttons) but i can't seem to edit anything now..

i don't know how i was able to associate those macros with the buttons

[Spudley_Jones]

in excel 2007 and in excel 2003, the user can rightclick the buttons and associate the button with the macro. you might be able to in the VB editer built in excel....

[globulous]

not sure how to do that.. as far as i can tell, i can't..

[Spudley_Jones]

is there a way to post the worksheet?

[globulous]

ok i think i've figured it out now...
i've found out how to edit the buttons.. i managed to fix 1 button, so now all i have to do is do another 59 more...

basically i have to re-assign the existing macro's to the button.. i don't know why.. i'll look at the code and see if it's any different... but so far so good..

[Spudley_Jones]

awesome! Good luck.

[globulous]

rite on, thanks for your patience and help!...

btw... is there any way to make those macros run anymore efficiently?

[Spudley_Jones]

yes. you can use the code I pasted earlier that was condensed or you can combine the copy/paste command into one line.

Please Login or Register  to view this content.

this line of code take the place of about 4 of the origional

[royUK]

The best way to create a custom menu or commandbar is to build it with code each time the workbook or addin is opened. See Add_Toolbar.zip

[DianeElmes]

A couple years ago i made a giant spreadsheet and had 2 custom toolbars to run macros from to manipulate the sheet.

This year i re-did the sheet, meaning all my old data was replaced (it's a giant schedule basically).

However, the Macros in the custom toolbars stayed exactly the same and performed the same function and are directed to the exact same cells, rows, columns, etc..

But now when i hit the buttons on the custom toolbars to run my macros, i get the following message... all the time...

**
A document with the name "NHLsheet.xls" is already open. You cannot open two documents
with the same name, even if the documents are in different folders.
To open the second document, either close the document that's currently open, or rename one of the documents.
**

Trouble is, i have only 1 document open!
There's something in the macros that thinks i'm trying to open a new document but i'm clueless as to where or why.. they are very simple macros
that do not require the opening of any new workbooks..

All they do is point to another sheet within my open workbook, "NHLsheet", copy a particular row from that sheet, and paste it to a specific spot onto another sheet within my workbook, "NHLsheet".


Here is a sample:

Please Login or Register  to view this content.

I think it has to do with the fact that i created these macro toolbars 2 years ago for use in the workbook i had used 2 years ago..

though nothin has really changed..

I can't figure out why it insists that there is another copy of "NHLsheet" open somewhere??

Can anyone help?

thanks,

I have the same issue and found that once I had unattached and then re-attached the toolbar the file worked OK.

The issue I have is that if I save the file with this tol bar to my personal drive the toolbar still looks for the toolbar on the shared drive, so I have to go through the whole process of unattaching and re-attaching everytime I open the file off of my hard drive. Other people have to do the same as well.

Any suggestions anyone would be greatly welcomed.

Thank you

[romperstomper]

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.