Disable checkbox on Custom ribbon on worksheet active or inactive

[krishnaoptif]

Hi guys
i want to enable checkboxes from the custom ribbon only for two sheets and for rest of sheet it should be disabled.

Please check below code which i have written.

<group id="Group2" label="SELECT OTHER VIEW OTPIONS"><checkbox id="Showfew" label="Show Few" getpressed="ShowCodes_getPressed" onaction="ShowCodes_onAction" getenabled="GetEnabled"><checkbox id="ShowOther" label="Show Other" getpressed="ShowAbbreviations_getPressed" onaction="ShowAbbreviations_onAction" getenabled="GetEnabled">This is my VBA Code to disable during workbook opening time. i have also written..

        Public Sub GetEnabled(control As IRibbonControl, ByRef returnedVal)
                If ActiveSheet.Name = "ItemCreator" Or ActiveSheet.Name = "ItemUpdator" Then
                    returnedVal = True
                Else
                    returnedVal = False
                End If
        End Sub

how can i run this GetEnabled public sub procedure on Worksheet_Activate() and Worksheet_Deactivate() events</checkbox></checkbox></group>

 

[krishnaoptif]

Hi Experts, It is so surprising that i did not receive any response yet on this..

This is really very urgent for me to finish asap..

Regards,
Krishna

 

[krishnaoptif]

can i get help in this ?

 

[Domenic]

Whenever a sheet is activated, you'll need to invalidate the checkbox control, which will cause the GetEnabled procedure to be called. Assuming that your code assigned the Ribbon object to a variable, let's say MyRibbon, first place the following code into the code module for ThisWorkbook...

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Call UpdateCheckBox
End Sub

Then include the following code with your other VBA callbacks (change the name of the checkbox accordingly)...

Sub UpdateCheckBox()
    'Executed when a sheet is activated
    MyRibbon.InvalidateControl ("Checkbox1")
End Sub

Hope this helps!

 

[krishnaoptif]

Hello Mr Domenic,
Thanks for the great help in changing the checkbox values basis on sheet change by using this code. but i want to know one more thing, if i will choose any other sheet apart from "ItemCreator" and "ItemUpdator" then my checkbox should be disabled...
Can you please provide code for else condition in below code ?

            Public Sub Checkbox1_getPressed(control As IRibbonControl, ByRef returnedVal)
                If ActiveSheet.Name = "ItemCreator" Then
                    returnedVal = TRUE
                ElseIf ActiveSheet.Name = "ItemUpdator" Then
                    returnedVal = FALSE
                Else
                   'here you need to provide the code to disable the checkbox.. or is there any other method to disable this ?


              
                End If
                
            End Sub

 

[Domenic]

In addition to getPressed, which changes the value for your checkbox, you can use the getEnabled VBA callback procedure to enable/disable the checkbox. First, add the following RibbonX code to your checkbox control...

getEnabled="Checkbox1_getEnabled"

Then you can use the following VBA callback procedure...

Public Sub Checkbox1_getEnabled(control As IRibbonControl, ByRef returnedVal)
    If ActiveSheet.Name = "ItemCreator" Then
        returnedVal = True
    ElseIf ActiveSheet.Name = "ItemUpdator" Then
        returnedVal = False
    End If
End Sub