Custom ribbon Menu
Hello
I have created a custom menu using CustomUI for Excel. In the menu I have a menu item that opens an excel template. The macro saves the file with the nomenclature that MUST be used and when the macro ends, the user can edit , review, print etc.. this works good. Once they have completed the excel form the completed form must be emailed to a group with specific instructions. I have a macro to do that. Once they have completed the form there is a menu option on the custom ribbon that does what must be done. This works good too
Everything works great, except when the user first runs the menu item to open the excel template , excel opens up the template back in the "HOME " excel standard menu item. IS it possible that when Excel opens the template it remains on my custom menu?
thanks
I have created a custom menu using CustomUI for Excel. In the menu I have a menu item that opens an excel template. The macro saves the file with the nomenclature that MUST be used and when the macro ends, the user can edit , review, print etc.. this works good. Once they have completed the excel form the completed form must be emailed to a group with specific instructions. I have a macro to do that. Once they have completed the form there is a menu option on the custom ribbon that does what must be done. This works good too
Everything works great, except when the user first runs the menu item to open the excel template , excel opens up the template back in the "HOME " excel standard menu item. IS it possible that when Excel opens the template it remains on my custom menu?
thanks
ASKER
Hi JK
thanks for the comments. I will give it a try. Perhaps I am trying to do something that can be easily accomplished another way,
I had been thinking about running the macro, pausing it for a time then continue on to the email process. But I am always worried about a timer when editing a excel spreadsheet.
BUT , I have been reading about a modeless form, I haven't quite got it all yet , but this is the way I thought it might work.
the user clicks on the macro, it stops displays a button called "EMAIL" the user can edit to his/her heart content , the macro doesn't continue till the user clicks the floating button to email the form.
Do I have that correct
thanks for the comments. I will give it a try. Perhaps I am trying to do something that can be easily accomplished another way,
I had been thinking about running the macro, pausing it for a time then continue on to the email process. But I am always worried about a timer when editing a excel spreadsheet.
BUT , I have been reading about a modeless form, I haven't quite got it all yet , but this is the way I thought it might work.
the user clicks on the macro, it stops displays a button called "EMAIL" the user can edit to his/her heart content , the macro doesn't continue till the user clicks the floating button to email the form.
Do I have that correct
I'd keep it simple. After the Display command, simply add a MsgBox line, possibly with YesNo button so the user can stop by pressing No.
Have you tried adding the xml to the Template using CustomUI Editor. Unless it's an addin then Custom Ribbons belong to the specific workbook.
So you can save your original workbook as an addin, that way the menu will always be visible. Alternatively add the Ribbon adaptions to the template workbook
So you can save your original workbook as an addin, that way the menu will always be visible. Alternatively add the Ribbon adaptions to the template workbook
ASKER
Hi
Yes , the custom UI , the macro are addiin.
the spreadsheet is a template
Yes , the custom UI , the macro are addiin.
the spreadsheet is a template
How many sheets are in the Template?
ASKER
there is only 1 sheet in the template.
Then could you have the sheet within the addin? When the user opens the addin with code make the addin a workbook, let the user complete the form and email then convert back to an addin
ASKER
Hi Roy
, someone had the same situation as me, a reply was posted:
Create a modeless form with nothing but an OK button on it. When the user runs the macro the code will execute up to the point where the user input is required and then the form will be displayed. Since the form is modeless you can move it and manually enter data into the workbook. When the user clicks the OK button the form should be hidden and the remainder of your macro code should be executed in the button_click event. To make the form modeless just open the form in the VB editor and set the ShowModal property to False.
Do you know how to do this
, someone had the same situation as me, a reply was posted:
Create a modeless form with nothing but an OK button on it. When the user runs the macro the code will execute up to the point where the user input is required and then the form will be displayed. Since the form is modeless you can move it and manually enter data into the workbook. When the user clicks the OK button the form should be hidden and the remainder of your macro code should be executed in the button_click event. To make the form modeless just open the form in the VB editor and set the ShowModal property to False.
Do you know how to do this
Yes, do you want to upload your workbook? Here's any example doing some useless stuff. Post back if you want more help
EE-Modeless-UserForm-to-halt-code.xlsm
EE-Modeless-UserForm-to-halt-code.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Duh, should have known about the activeatetab method. Silly me!
I should also point out that whilst all I have said is true you need to be careful of 'Sate loss'. This is were Excel encounters some kind of error / issue and it loses the current sate of all it's variables. This doesn't always generate an on-screen error but would mean that your internal global variable that holds the ribbon ID would be emptied and therefore you would not be able to activate your tab.
The way to beat this is to use the API to copy the contents of your variable's memory address to a named range which makes the data persistent no matter what and allows the ribbon variable to be restored if lost.
The way to beat this is to use the API to copy the contents of your variable's memory address to a named range which makes the data persistent no matter what and allows the ribbon variable to be restored if lost.
ASKER
Thanks for your suggestion and recommendation! Appreciate your help
So load your template and hold the alt key. Note the shortcut Excel displays on top of your tab.
Now somewhere near the end of your startup code, add:
Application.SendKeys "%YourShortCutKeyGoesHere"
DoEvents