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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

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,205
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,205
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,102,274
Messages
5,485,783
Members
407,515
Latest member
franjey

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top