PDA

View Full Version : [SOLVED] Ribbon Issue



zoom38
03-02-2016, 07:31 AM
I created a custom group on the ribbon that is associated with some macros. I only want this group to be in a certain file but it is now permanent. Does anyone know how do I make it so the group on the ribbon I created only shows and works from a specific file? It occurs in both excel 2010 and 2013.

mancubus
03-02-2016, 08:06 AM
http://www.rondebruin.nl/win/s2/win002.htm

check the examples linked at the bottom of the page.

zoom38
03-02-2016, 08:12 AM
Thanks for the reply. Unfortunately I am working on a network where I don't have permission to download the UI editor. Was much easier in Excel 2003 in the open and close events.

Bob Phillips
03-02-2016, 08:42 AM
Excel files are just zipped files, so you could create a file called customUI.xml with the appropriate xml in it, put that in a folder called customUI. Then change your Excel workbook file extension to .zip, open it with a Zip file editor, and drop that customUI into the zipped file, then rename it back. Convoluted, but should work if you can't get hold of the editor.

zoom38
03-02-2016, 08:46 AM
Thanks XLD but it sounds like more or a hassle than need be. I have Excel 2007 on my personal computer and I will download the UI to it and work from there.

Bob Phillips
03-02-2016, 03:13 PM
It is, a lot more, but if you don't have a screwdriver you have to use the hammer.

zoom38
03-03-2016, 06:06 AM
Thank you both for your assistance. I was able to avoid using the "hammer" by using the editor on my home computer in Excel 2007 and it works on my work computer in Excel 2013.
*Solved*

Gary

zoom38
03-03-2016, 08:21 AM
I marked it solved too soon. After using the editor and adding (control As IRibbonControl) to the appropriate sub header things work great on the ribbon. However, I found out that when other subs call one of the subs with the (control As IRibbonControl) it pops a compile error - "Argument not Optional". Adding (control As IRibbonControl) to the sub call that fails then gives a compile error - "Expected: list separator or )".
Is there a way to call this sub from another routine?
Or can it now only be used from the ribbon and I have to duplicate the sub and give it another name?

Gary

Bob Phillips
03-03-2016, 04:06 PM
Surely, the problem is that the callback routine is passed a ribbon control. If you want to call that routine, you need to pass the control, which of course you don't have access too. You could fake it, if you don't use the control in your code, just pass any old dummy object, see what happens.

Bob Phillips
03-03-2016, 04:07 PM
The other way is to have the main code in a procedure, and cal that from the callback procedure. You can call the main code from anywhere then.

Paul_Hossler
03-03-2016, 04:54 PM
I marked it solved too soon. After using the editor and adding (control As IRibbonControl) to the appropriate sub header things work great on the ribbon. However, I found out that when other subs call one of the subs with the (control As IRibbonControl) it pops a compile error - "Argument not Optional". Adding (control As IRibbonControl) to the sub call that fails then gives a compile error - "Expected: list separator or )".
Is there a way to call this sub from another routine?
Or can it now only be used from the ribbon and I have to duplicate the sub and give it another name?

Gary


Might be quicker to post a small workbook with just enough code (callbacks, XML, etc.) to see the issue. Probably don't even need any data

zoom38
03-04-2016, 10:44 AM
XLD I used a dummy object as you suggested and it worked. I was able to use the sub from the ribbon and from within another sub without error. I don't really understand why it would be necessary to add (control As IRibbonControl) to a sub for the ribbon to work in the first place. Seems to be unnecessary if adding (dummy) to the sub works.

Paul I attached a small workbook to illustrate the issue I was experiencing.

Thank you all for taking the time to work on my problem.
"Solved"

Gary

Bob Phillips
03-04-2016, 04:01 PM
Gary, it is needed because very often you want to access the control directly. For instance, I have a generic routine that I use for all of my callbacks that set the ribbon control's label. That same routine would be called for every control, so I take the control.Id and use it as my lookup value that determines the label caption to use.

Paul_Hossler
03-04-2016, 04:06 PM
OK, but I'd keep the callback as a 'ribbon processor' and the calendar shower in another module (probably personal style) to avoid a dummy object



Option Explicit

'Callback for customButton2 onAction
Sub Pop_Cal2(control As IRibbonControl)
Show_Cal
End Sub




and




Option Explicit

Sub Show_Cal()
Load C_Form
C_Form.Caption = "Pop Calendar V6"
' C_Form.HelpLabel.Caption = "Insert Your Help Lable"
C_Form.Show
Unload C_Form

End Sub

Bob Phillips
03-06-2016, 07:18 AM
OK, but I'd keep the callback as a 'ribbon processor' and the calendar shower in another module (probably personal style) to avoid a dummy object

I gave the OP both options, but I think I would personally go the same way as you Paul.

Paul_Hossler
03-06-2016, 08:17 AM
@XLD -- Yes (your #10), but since 1 JPG = 1K TXT, I thought some examples would be helpful (as well as FWIW a personal opinion)