Is there a way to avoid ambiguity in running macros (subroutines) that have identical names in multiple open workbooks? To further explain, I have two macro enabled workbooks opened. Both have a VBA subroutine named "Test" that is executed by either a button click or a hot key. In some cases, when workbook 'A' is active and 'Test' is launched with a hot key, the 'Test' subroutine in workbook 'B' is executed instead. Is this an Excel problem? Is there a way that I can ensure that the subroutine associated with the active workbook is the one executed and not the 'inactive' one? Thanks.
use fully qualified macro name
ie specify the workbook name as well
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
How do you do that? When I customize the Quick Access Table, adding the button and assigning a macro to it, I can't see any way to specify the workbook name.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
The macro is not launched by VBA code. It is attached to an icon on the QAT. When I customize the QAT, there is no option to specify a workbook name (at least as far as I can see). The window that allows customization of the QAT only lists macros that are available to which you can assign the icon (button). This list only presents the macro name. There is no way to specify workbook name, at least from what I can see. There has to be another way to eliminate this ambiguity or this is an Excel bug. Agree?
i am sure a similar provision is available for QAT
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Edit:#1 I discovered that I have a bug in this...for some reason it's still running the wrong macro.. Edit#2: Bug seems to be solved by moving code out of the ThisWorkbook Events to a macro and calling it.
All of this is a bulky way of doing it I know, and may be susceptible to some issues, but hopefully it will motivate someone to come up with something better all the faster.
Using a drop-down list located within the QAT, instead of a button allows me to run vba to reference the macro to the active workbook.(Edit:
other QAT buttons are not deleted or affected by the this code.)
Right click on the QAT > Click Customize Quick Access Tool bar
> Choose All Commands from the drop down > Select "Menu Commands" move "Menu Commands" to the right using the add button,
Click ok, then the code below will add your macro to the QAT Menu Commands drop down when the workbook is open or activated.
In the ThisWorkBook Code Module
Code:
Private Sub Workbook_Open()
CreateQATdropDownMenu
End Sub
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
'Allows two open workbooks to run their own version of the macro.
CreateQATdropDownMenu
End Sub
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
'reset menu so other worbooks do not have macro assigned.
Application.CommandBars("File").Reset
End Sub
In a regular Module
Code:
Sub CreateQATdropDownMenu()
CommandBars("File").Reset 'Reset to prevent multiple copies
With CommandBars("File").Controls.add(msoControlButton, , , , True)
.Caption = "MyMacro"
.OnAction = "'" & ThisWorkbook.Name & "'!MyMacro"
End With
End Sub
Edit#3: I'm not sure, but I think that this is an issue with running an .xls file from Excel 2007. Even if you need to keep it as an .xls for compatibility, as a test you may want to convert the file to an .xlsm to see what happens. I believe there is a way to have one of the .xml files within the .xlsm to retain macro assignment's as either local or global.
There is some info about that at this link: http://blogs.msdn.com/b/excel/archiv...on-images.aspx
At the site referenced above there is also a link to rondebruin's site, who is highly regarded for the info he provides about the QAT and the Ribbon.
Last edited by SQLADOman; Sep 14th, 2010 at 11:33 AM.
More complicated than I had hoped and I'd rather not do away with the QAT buttons to which the users are accustomed. Is there any way to check activeworkbook.name to validate and force the macro that is launched?
This issue got raised when the user had two workbooks open. Both workbooks are very similar but report data against two different companies. The workbooks contain a hidden sheet that contains company name. That field is used to generate a file name and date which are used to save the workbook when the QAT button is clicked. The user clicked the 'save file' button when workbook 'Company A' was active, but it looks like the 'save file' macro for workbook 'Company B' was executed instead. This caused workbook 'Company A' to be saved as workbook 'Company B'. Not good!
I've cautioned the user to avoid having both workbooks open when saves are done, but that is a bit restraining. I need a way to force these workbooks to use macros contained within their own modules.
You might want to look here: http://www.rondebruin.nl/imageqat.htm
Scroll down about 1/3 of the page to: "How do you customize the QAT for one workbook"
It does seem that you can have a QAT that is unique to individual workbooks if you are using the .xlsm format,
but not if you are using the.xls format, and it certainly involves a lot of work.
I don't pocess enough of a great brain to offer any other ideas at the moment, but likely one of the experts here can.
Good luck.
Last edited by SQLADOman; Sep 14th, 2010 at 03:15 PM.
Reason: Added Scroll down about 1/3 of the page to: How do you customize the QAT for one workbook
It is a little tricky to follow but is explained accurately enough and no add-in is required and if you go slow and do everything correctly it really is not very complicated.
The result is as many buttons with macros assigned as you want on the QAT for that workbook only and will only run the local macro regardless of other workbooks with the same macro names, or even with the same buttons, even if several copies are running at the same time.
With my test I added three buttons with three different macros, then saved the workbook by any name I wanted.I also made several copies of the workbook, using any name I wanted and all the copies became independent of each other, without any xml editing to the copies.
It is worth noting again that: The instructions must be followed very precisely. It is easier to avoid mistakes if you try to avoid using underscores in your workbook names or your macro names, because in the xml doc: path that needs editing, an underscore and a one _1, are automatically addedto the end of the Macro name, and those must be left intact.
Also, during the manual customizations you do to the QAT's buttons and macro assignments, it is critical to select "your workbook name" from the drop down at the upper right of the dialog. -->If you forget and leave the default "For all documents", you'll likely become confused and frustrated with the improper result and with correcting it.
I've attached a sample workbook. You will notice that you can save copies of it by other names and each will run only the local macro/'s, and as I mentioned before, other copies with the same macro names can be running at the same time with no problem and without additional xml editing..
Last edited by SQLADOman; Sep 14th, 2010 at 07:54 PM.
Reason: added attachment and Corrected some misspells and added some highlighting to important words or phrases.
Please post back with how it works out, or with any issues, as now that I played with it a lot, I can probably be of assistance. I will though be gone for several hours.
Interesting. The link you sent me, http://www.rondebruin.nl/qatbuttonbug.htm, addresses a problem I had posted a while back regarding parsing errors and missing buttons. I discovered, by experimenting with the xml, that removing the workbook path from the xml, as described in the link, fixed the problem. As a matter of fact, when I add a button to your MyTest-1 workbook, close the book and reopen it, I get the same parsing error. I think I had this problem isolated to a particular KB that was added to my system by Windows Update. Since then I have not been able to uninstall the KB as it is strangely not listed in Control Panel installed programs although Windows Update says it's installed. Now, everytime I add a button to QAT, I have to go through this edit xml process. You can search on my user name to see those threads.
Getting back, though, to the subject of this thread, I'm not understanding how that link applies. Are you saying that the workbook pathname has to be embeded in the xml, and that is what restricts execution to macros within the workbook?
Yes it is the embedded customUI.xml file that is the issue. (bug) Edit: but we want to parse out the workbook path, not add it.
For example in the two partial string's below,
We need to remove any _ or ! at the end of a file path
and after that we then remove the entire path and file name.
C:_Users_Owner_Desktop_MyTest-1.xlsm_
C:\Users\Owner\Desktop\MyTest-1.xlsm!
Any other underscores we do not touch.
For example in the sample workbook that I attached the macro I named MyFirstMacro
automatically has an underscore and a one in the xml file and must remain there. MyFirstMacro_1
The customUI.xml file is supposed to be maintained correctly automatically, but from what I read became a bug in SP2.
I found the same thingthat you did, when adding a new button to the QAT. It is only when saving copies by different workbook names with no changes or additions to the QAT macro buttons, that does not require xml editing.
It would though require an even bigger task with the various macro versions that would need to be updated. Probably manually... Oh well, I thought we were getting close, but I now see we're not.
I think Ron describes at the link, to go back to before any Excel 2007 service packs.. However, it goes without saying, many security risks would return and 100's of service pack improvements lost.
-- Being that you said that the individual kb fix that may be causing the bug, is not in the uninstall programs list, I suppose it's worth considering to look for a tool to uninstall it.
- At one of the many post's that I found at various forums, the person thought that
the relevant kb patch might be security patch KB969682, but he was not sure,
is that the same as what you think?
- - The better choice of course, (if you possibly could), would be to upgrade to 2010. I've heard there are a lot of nice improvements, including this bug fixed. Two things with 2010 that interest me a lot are some charting property additions and Excel 4.0 (XLM) macro related enhancements. - Woops, got side tracked a minute, back to your situation, it seems that the saving's in time might cause 2010 to pay for itself.. Even if your company has dozens of computer's.
Edit: Of course there is still a chance that an Expert has a simple solution, so I'll pray with you for that, as I could make good use of it also.
Last edited by SQLADOman; Sep 15th, 2010 at 04:06 AM.
Yes Koolsid, using an add-in was my thought earlier, and what is described at the link I provided in post#10
You might want to look here: http://www.rondebruin.nl/imageqat.htm
Scroll down about 1/3 of the page to: "How do you customize the QAT for one workbook"
But there are similar workbooks that use the same macro names and the same QAT buttons,
but different version's of the macro's.
Correct me if I'm wrong, but I am guessing he would need a different add-in for each similar workbook, which would still create somewhat of a nightmare when he adds more button's or edit's macro versions on an individual workbook, and when he distributes to other pc's.
Edit: @doasidont, about how many workbooks and different pc's are involved? and how often do the similar workbooks have some different QAT buttons?, and approx. how many macro names? are unique to only one, or some, not all of the similar workbooks?
Last edited by SQLADOman; Sep 15th, 2010 at 03:59 AM.
KB969682 is the culprit I believe. At one point I was able to back out the update set that included this KB. I then added one at a time and tested. When this one was added, the problem appeared.
Excel 2010 not an option at this time.
About half a dozen workbooks and a few dozen PC's. The similar workbooks all have the same QAT buttons with no unique ones.
To switch gears here for a minute, let me again raise the issue that prompted this thread in the first place. Somehow exact copies of the same workbook got saved using two different file names. Workbooks are saved using a QAT button that calls a macro within the workbook that retrieves the report name from one of its sheets. The macro then formats a file name and does the save. It's unlikely that the user manually did a copy of one report and pasted to a different report name (which would explain two exact copies under different names). If they in fact used the QAT button, then the only thing I can figure is that two similar workbooks (e.g., 'A' and 'B') were open, the user clicked the QAT button to save 'A', but Excel grabbed the macro from workbook 'B' and executed it instead. That would cause a copy of workbook 'A' to be saved as 'B'. I've tested this scenario over and over but cannot duplicate this problem. Hence my thread here asking about macro name ambiguity. I have encountered situations where multiple xlsm workbooks were open and these workbooks had macros that used the same hot keys. In some cases, hitting a hot key invoked the wrong macro. But I've never had this occur when launching a macro using the QAT.
The fact that the set of QAT buttons is always the same is certainly good news.
[doasidont wrote:] I have encountered situations where multiple xlsm workbooks were open and these workbooks had macros that used the same hot keys. In some cases, hitting a hot key invoked the wrong macro. But I've never had this occur when launching a macro using the QAT.
How sure are you of the above?
With my experimenting the QAT macro's were causing another workbook to open and run it's code.
If the problem only occurs while using hot key's, maybe we should be looking at that.
Are the hot keys set using vba Application.OnKey ?
About how many of the macros have different versions?, and how sure are you that they can not be re-written to use some sort of select case style of coding to make them universal to all the similar workbooks?
Is there anything in the workbooks name, or a sheet name or a cell heading or a cell value that could possibly be used to determine what processing should be done?
Last edited by SQLADOman; Sep 16th, 2010 at 02:25 AM.
Reason: Added a couple of question's, and re-wrote most of the question's because some elements to them had already been answered.
Reply With Quote
Resolved", if the query is solved
