Results 1 to 6 of 6

Thread: Disable checkbox on Custom ribbon on worksheet active or inactive

  1. #1
    Board Regular krishnaoptif's Avatar
    Join Date
    Sep 2010
    Location
    INDIA
    Posts
    139
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Disable checkbox on Custom ribbon on worksheet active or inactive

    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.

    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
    Last edited by krishnaoptif; May 13th, 2019 at 03:27 PM. Reason: putting xml there

    Regards,
    Krishna Chaudhary

    ________________

  2. #2
    Board Regular krishnaoptif's Avatar
    Join Date
    Sep 2010
    Location
    INDIA
    Posts
    139
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Disable checkbox on Custom ribbon on worksheet active or inactive

    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

    Regards,
    Krishna Chaudhary

    ________________

  3. #3
    Board Regular krishnaoptif's Avatar
    Join Date
    Sep 2010
    Location
    INDIA
    Posts
    139
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Disable checkbox on Custom ribbon on worksheet active or inactive

    can i get help in this ?

    Regards,
    Krishna Chaudhary

    ________________

  4. #4
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,637
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Disable checkbox on Custom ribbon on worksheet active or inactive

    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!

  5. #5
    Board Regular krishnaoptif's Avatar
    Join Date
    Sep 2010
    Location
    INDIA
    Posts
    139
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Disable checkbox on Custom ribbon on worksheet active or inactive

    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 by krishnaoptif; May 18th, 2019 at 08:40 AM.

    Regards,
    Krishna Chaudhary

    ________________

  6. #6
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,637
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Disable checkbox on Custom ribbon on worksheet active or inactive

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •