I am creating a menu bar using code like the below in excel 2013 and 2010 in an addin
is there a way for me to specify which sheet (session) the menu will appear in?
Please Login or Register to view this content.
the issue is that I have a temporary excel file I want it to be assigned to but it keeps assigning it to another sheet.
Here is sample code creating a menu bar add-in that activates when the workbook is opened and deactivates when the workbook is closed. Couldn't this be done for your needs by placing the code into Worksheet Initialize or Activate ... then Worksheet Deactivate ?
I haven't tested the code.
Please Login or Register to view this content.
Yes that is pretty similar to how I currently have it working.
Unfortunately this problem occurs in a specific situation where Another application (SAP) is opening an excel file as a temporary file. When excel tries to assign the menu by default it is putting it in the wrong session (maybe that is an error on SAP's behalf but i need to work around it). This was fine for 2010 because excel opened multiple files in a single session - now however with 2013 there is an issue.
I was hoping that I could override the default by specifying the session or the file that the menu should be associated with. Or another option is to create the menus in an add-in that i have installed in all the users computers (which of course does display as it displays in all excel sessions) and then maybe hide it and unhide it when this sheet is loaded
ie set my
then in the sheet I open setPlease Login or Register to view this content.
However I am still having trouble referring to the command bar... maybe it is still looking for it in the wrong session.Please Login or Register to view this content.
Change toPlease Login or Register to view this content.
... or similar ?Please Login or Register to view this content.
Found this on LINK
As I understand what the author is saying, your challenge is due to Excel versions earlier to 2013 / 2016. If he is accurate, seems like its time to upgrade versions ?WHAT’S DIFFERENT IN EXCEL 2013 AND EXCEL 2016?
If you’ve used VBA to work with shortcut menus in Excel 2007 or earlier, you need to be aware of a significant change.
In the past, if your code modified a shortcut menu, that modification was in effect for all workbooks. For example, if you added a new item to the Cell right-click menu, that new item would appear when you right-clicked a cell in any workbook (plus other workbooks that you open later on). In other words, shortcut-menu modifications were made at the application level.
Excel 2013 and Excel 2016 use a single document interface, and that affects shortcut menus. Changes that you make to shortcut menus affect only the active workbook window. When you execute the code that modifies the shortcut menu, the shortcut menu for windows other than the active window will not be changed. This is a radical departure from how things used to work.
Another twist: If the user opens a workbook (or creates a new workbook) when the active window displays the modified shortcut menu, the new workbook also displays the modified shortcut menu. In other words, new windows display the same shortcut menus as the window that was active when the new windows were opened.
Bottom line: In the past, if you opened a workbook or add-in that modified shortcut menus, you could be assured that the modified shortcut menus would be available in all workbooks. You no longer have that assurance.
Last edited by Logit; 11-06-2016 at 08:56 PM.
yes I think this is exactly the issue...Originally Posted by Logit
Update my version of SAP? Yes that would be nice but sadly that would be prohibitively expensive.
The excel on the other hand - the update is apparently the thing causing problem. But of course people want to update that and other systems due to support running out for old versions of software etc.
I tried playing around with
Please Login or Register to view this content.etc - but no luck...Please Login or Register to view this content.
I have managed to get pretty close... I can create the bar using my addin, and then unhide it when required. But the tool bar needs to link to macros in the sheet i open and i don't know the name of that sheet until it is open (which will be some jumble of letters and numbers like ~SAP23423542) so i don't know how to assign it's macros to the toolbar. (otherwise I'd just write "~SAP23423542!macroA").
If I try to assign the buttons after the sheet is open (i.e. when I can tell what the file name is by using the activesheet.name) i seem to run into my same problem of not being about to talk properly to the toolbar - it complains as follows "method add of object 'commandbarcontrols' failed"
BTW I really appreciate having another mind look at this.
Last edited by scottiex; 11-06-2016 at 10:45 PM.
I would presume there is a workaround out there somewhere but (admittedly) coding for that is above my knowledge level.
Perhaps one of the gurus here will assist.
Wish I could more for you.
ps: would love to visit your country.
Hi.
I inagine your toolbars could simply run macros that do something along these lines:
Please Login or Register to view this content.
Don
Please remember to mark your thread 'Solved' when appropriate.
Unfortunately the toolbar doesn't know the name of the temporary file that will be there when it creates the toolbar for the add in, and if it is run form the temporary file it seems to think I want any tool bar it adds added to a different sheet, and it cant seem to edit the existing add-in toolbar. Although its possible I'm doing something wrong there.
However, I have a workaround that seems to be working - I have entered all the code from the temporary files in the addin (quite a bit of code) and the user can select in the add in what tool bar they want to show. Not really as neat as talking to the macros in the temporary file - But this seems to be working so far.
The add-in does not need to know the name of the file. It simply contains several macros that do nothing more than call Application.Run with the name of the macro in the temporary file. The activeworkbook.name part will get the name of the file at run time, whatever it happens to be.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks