Disable checkbox on Custom ribbon on worksheet active or inactive

krishnaoptif

Board Regular
Joined
Sep 17, 2010
Messages
140
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..


Code:
        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>
 
Last edited:

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

krishnaoptif

Board Regular
Joined
Sep 17, 2010
Messages
140
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
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,139
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...

Code:
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)...

Code:
Sub UpdateCheckBox()
    'Executed when a sheet is activated
    MyRibbon.InvalidateControl ("Checkbox1")
End Sub
Hope this helps!
 

krishnaoptif

Board Regular
Joined
Sep 17, 2010
Messages
140
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 ?

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
 
Last edited:

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,139
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...

HTML:
getEnabled="Checkbox1_getEnabled"
Then you can use the following VBA callback procedure...

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,096,308
Messages
5,449,601
Members
405,573
Latest member
Diogo Martins

This Week's Hot Topics

Top