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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Andy Pope

Board Regular, The other chart guy
Joined
Dec 31, 2003
Messages
348
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 >
 

Andy Pope

Board Regular, The other chart guy
Joined
Dec 31, 2003
Messages
348

ADVERTISEMENT

No idea why the use of "o nLoad", without a space, should result in ******
 

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
 

Andy Pope

Board Regular, The other chart guy
Joined
Dec 31, 2003
Messages
348

ADVERTISEMENT

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
 

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******">
 

Andy Pope

Board Regular, The other chart guy
Joined
Dec 31, 2003
Messages
348
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
 

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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,623
Messages
5,512,459
Members
408,898
Latest member
Glenno557

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top