Ribbon GetEnabled Callback

nosdude

New Member
Joined
Jun 3, 2010
Messages
7
Hi,

I've been battleing over this for sometime now. I've created a custom ribbon with a range of buttons and dropdown menus. So far I have been able to get code to run when using OnAction commands for buttons, but I am now trying to use Callbacks to change the enabled state of a dropdown or change the item selected in the dropdown - but this whole business of callbacks and how to use them is really confusing me.

For example I am trying to enable a dropdown called Audience when the sheet called Audience is selected.

This is my code for the Callback

Code:
Public Sub AudienceGetEnabled(control As IRibbonControl, ByRef returnedVal)
'
' Code for getEnabled callback. Ribbon control dropDown
'

If ActiveSheet.Name = "Audience" Then
    returnedVal = Enabled
End If
    

End Sub

And this is the code I have when the sheet Audience is selected.

Code:
Private Sub Worksheet_Activate()
AudienceGetEnabled
End Sub

I get an 'Argument not Optional' error when trying to run this Worksheet_Activate code, which from what I've been able to find out online has something to do with the ByRef argument given in the AudienceGetEnabled sub.

I also thought this might work on the Worksheet_Activate code but I get an Object required error

Code:
Private Sub Worksheet_Activate()

control.AudienceGetEnabled.returnVal = Enabled

End Sub


Any help would be greatly appreciated.

Thanks
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Andy Pope

Active Member
Joined
Dec 31, 2003
Messages
349
You need to use 2 callbacks. One for the GetEnabled for the dropdown list Audience, which you have. The other for the CustomUI ******.

Add the following to a standard code module.

Code:
Option Explicit

Public Const gRBX_CONTROL_NAME = "Audience"
Public g_rbxIRibbonUI As IRibbonUI
Public g_blnEnabled As Boolean
Public Sub Audience_getEnabled(control As IRibbonControl, ByRef returnedVal)
'
' Code for getEnabled callback. Ribbon control dropDown
'
    returnedVal = g_blnEnabled
    
End Sub
Public Sub rbxRibbon_******(ribbon As IRibbonUI)
'
' Code for ****** callback. Ribbon control customUI
'
    Set g_rbxIRibbonUI = ribbon
    
End Sub

Add the following to the Thisworkbook object
Code:
Option Explicit

Private Sub Workbook_Open()

    If ActiveSheet.Name = "Audience" Then
        g_blnEnabled = True
    Else
        g_blnEnabled = False
    End If
    
End Sub

And this to the Audience sheet object

Code:
Option Explicit

Private Sub Worksheet_Activate()

    g_blnEnabled = True
    g_rbxIRibbonUI.InvalidateControl gRBX_CONTROL_NAME
    
End Sub

Private Sub Worksheet_Deactivate()

    g_blnEnabled = False
    g_rbxIRibbonUI.InvalidateControl gRBX_CONTROL_NAME

End Sub

The ribbonx looks like this

< customUI
xmlns="http://schemas.microsoft.com/office/2006/01/customui"
******="rbxRibbon_******">
< ribbon >
< tabs >
< tab idMso="TabHome" >
< group
id="Group1"
label="Group1">
< dropDown
id="Audience"
label="Dropdown1"
getEnabled="Audience_getEnabled">
< item
id="Item1"
label="Item1"/>
< item
id="Item2"
label="Item2"/>
< item
id="Item3"
label="Item3"/>
< /dropDown >
< /group >
< /tab >
< /tabs >
< /ribbon >
< /customUI >
 
Upvote 0

Andy Pope

Active Member
Joined
Dec 31, 2003
Messages
349
No idea why the use of "o nLoad", without a space, should result in ******
 
Upvote 0

nosdude

New Member
Joined
Jun 3, 2010
Messages
7
Thanks Andy.

I set up my file to mimmic yours in a large way. I think my big problem was that I needed to use Public variables. However I'm running into an error when trying to invalidate the ribbon (if I understand correctly this is meant to refresh the ribbon).

I've tried to simplify the invalidate command to just refresh the entire ribbon... here is my code

Code:
Option Explicit

Public Rib As IRibbonUI
Public AudEnabled As Boolean

Public Sub Ribbon******(Ribbon As IRibbonUI)
'
' Code for ****** callback. Ribbon control customUI
'
Set Rib = Ribbon


End Sub



Sub RefreshRibbon()

Rib.Invalidate

End Sub

* As your last post pointed out for some reason On Load is being replaced by ******

Then on my Object code for the Audience sheet I have this code

Code:
Option Explicit

Private Sub Worksheet_Activate()

AudEnabled = True

RefreshRibbon

End Sub

However every time I get to the RefreshRibbon I get an error "Object variable or With block variable not set"

Have I failed to define a public variable or something like that?


Thanks for your help
 
Upvote 0

Andy Pope

Active Member
Joined
Dec 31, 2003
Messages
349
Can I assume that as well as adding the on Load routine to the workbook you have updated the ribbon xml to enable the o nLoad event.

It's part of the first tag with lots of ****'s
 
Upvote 0

nosdude

New Member
Joined
Jun 3, 2010
Messages
7
Sorry I should have included that, yes in the xml I have the On Load declared

<
customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"
******="Ribbon******">
 
Upvote 0

Andy Pope

Active Member
Joined
Dec 31, 2003
Messages
349
Not sure whether you have made all the changes, but this works for me.

Audience sheet object
Code:
Private Sub Worksheet_Activate()

AudEnabled = True
RefreshRibbon
    
End Sub

Private Sub Worksheet_Deactivate()

AudEnabled = False
RefreshRibbon

End Sub

code module

Code:
Public Rib As IRibbonUI
Public AudEnabled As Boolean

Public Sub Audience_getEnabled(control As IRibbonControl, ByRef returnedVal)
'
' Code for getEnabled callback. Ribbon control dropDown
'
returnedVal = AudEnabled
End Sub
Public Sub rbxRibbon_******(ribbon As IRibbonUI)
'
' Code for ****** callback. Ribbon control customUI
'
Stop
    Set g_rbxIRibbonUI = ribbon
    Set Rib = ribbon

End Sub
Sub RefreshRibbon()

Rib.Invalidate

End Sub
 
Upvote 0

nosdude

New Member
Joined
Jun 3, 2010
Messages
7
Thanks for you help Andy.

I think i solved my problem. I had another event running when the workbook opened. That event went and did something on the Audience sheet. Once I changed Application.EnableEvents to False during that opening sequence everything seem to work.
 
Upvote 0

Forum statistics

Threads
1,186,559
Messages
5,958,489
Members
438,361
Latest member
mihi78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top