Hide button on custom ribbon

[sharky12345]

Guys,

I have eventually managed to work out how to add a custom Ribbon with custom buttons in my project, but I am really struggling to understand how to do 2 things;

1) Hide certain buttons if certain criteria is met
2) Disable certain buttons if certain criteria is met

I have followed a variety of methods available on the internet, including Ron de Bruin's site, but I cannot get any of the examples to work, so can someone help me out and give me some pointers?

These are the callbacks in the file;

'Callback for customButton1 onActionSub CAIMUpdate(control As IRibbonControl)
'LIVE Mode
    On Error GoTo HandleError
10  If Sheet8.range("B28").Value = "ENABLED" Then
20      ActiveWorkbook.Connections("CAIM").Refresh
30      Application.ScreenUpdating = True
40      Call MsgBox("CAIM update complete", vbInformation, "Update completed")
50  End If
    'DEMO Mode
60  If Sheet8.range("B28").Value = "DISABLED" Then
70      Application.ScreenUpdating = True
80      Call MsgBox("CAIM update complete", vbInformation, "Update completed")
90  End If
    Exit Sub
HandleError:
    ErrorHandle Err, Erl(), "CAIMUpdate - RibbonMOD"
    Resume Next
End Sub

'Callback for customButton2 onAction
Sub Setup(control As IRibbonControl)
    On Error GoTo HandleError
10  SetupFrm.Show
    Exit Sub
HandleError:
    ErrorHandle Err, Erl(), "TeamSetup - RibbonMOD"
    Resume Next
End Sub

'Callback for customButton3 onAction
Sub EditCall(control As IRibbonControl)
    On Error GoTo HandleError
10  VehiclesFrm.Show
    Exit Sub
HandleError:
    ErrorHandle Err, Erl(), "EditCallsigns - RibbonMOD"
    Resume Next
End Sub

'Callback for customButton4 onAction
Sub DutyForm(control As IRibbonControl)
    On Error GoTo HandleError
10  Application.ScreenUpdating = False
20  Sheet3.Activate
30  Sheet8.Activate
40  Sheet9.Activate
50  Sheet4.Activate
60  Application.ScreenUpdating = True
70  DutyFrm.Show
    Exit Sub
HandleError:
    ErrorHandle Err, Erl(), "DutiesForm - RibbonMOD"
    Resume Next
End Sub

'Callback for customButton5 onAction
Sub AbstractionHours(control As IRibbonControl)
    AbstractionsSelectFrm.Show
End Sub

'Callback for customButton6 onAction
Sub ClearDuties(control As IRibbonControl)
    On Error GoTo HandleError
10  Application.ScreenUpdating = False
20  Sheet9.range("A2:C7").ClearContents
30  Sheet9.range("E2:G28").ClearContents
40  Sheet9.range("I2:K28").ClearContents
50  Sheet9.range("S2:U41").ClearContents
60  Sheet16.range("A2:D29").ClearContents
70  Sheet16.range("G1").ClearContents
    'Clear Formats
80  For Each cell In Sheet16.range("A4:A29")
90      cell.Interior.ColorIndex = xlColorIndexNone
100     cell.Offset(0, 1).Interior.ColorIndex = xlColorIndexNone
110     cell.Offset(0, 2).Interior.ColorIndex = xlColorIndexNone
120     cell.Offset(0, 3).Interior.ColorIndex = xlColorIndexNone
130     cell.Borders.LineStyle = xlNone
140     cell.Offset(0, 1).Borders.LineStyle = xlNone
150     cell.Offset(0, 2).Borders.LineStyle = xlNone
160     cell.Offset(0, 3).Borders.LineStyle = xlNone
170 Next
    'LIVE Mode
180 If Sheet8.range("B27").Value = "LIVE" Then
190     LoadResources
200     Sheet8.Activate
210     Sheet9.Activate
220     Sheet4.Activate
230     Application.ScreenUpdating = True
240 End If
    'DEMO Mode
250 If Sheet8.range("B27").Value = "DEMO" Then
260     LoadDemoResources
270     Sheet8.Activate
280     Sheet9.Activate
290     Sheet4.Activate
300     Application.ScreenUpdating = True
310 End If
    Exit Sub
HandleError:
    ErrorHandle Err, Erl(), "ClearDuties - RibbonMOD"
    Resume Next
End Sub

'Callback for customButton7 onAction
Sub DeveloperAccess(control As IRibbonControl)
    Dim MyPassword
    On Error GoTo HandleError
10  If Environ("UserName") = "Sharky" Then
20      ActiveWindow.DisplayWorkbookTabs = True
30      Application.ScreenUpdating = True
40      Sheet4.Activate
50      Sheet4.Visible = xlSheetVisible
60      Sheet1.Visible = xlSheetVisible
70      Sheet3.Visible = xlSheetVisible
80      Sheet4.Visible = xlSheetVisible
90      Sheet5.Visible = xlSheetVisible
100     Sheet8.Visible = xlSheetVisible
110     Sheet9.Visible = xlSheetVisible
120     Sheet13.Visible = xlSheetVisible
130     Sheet16.Visible = xlSheetVisible
140     Select Case MsgBox("Current mode is: " & Sheet8.range("B27").Value _
                         & vbCrLf & "" _
                         & vbCrLf & "Do you want to change it?" _
             , vbYesNo Or vbExclamation Or vbDefaultButton1, "Mode change")
        Case vbYes
150         DeveloperFrm.Show
160     Case vbNo
170     End Select
180     ActiveWindow.DisplayWorkbookTabs = True
190     Exit Sub
200 End If
210 If Environ("UserName") = "Sharky" Then
220     ActiveWindow.DisplayWorkbookTabs = True
230     Application.ScreenUpdating = True
240     Sheet4.Activate
250     Sheet4.Visible = xlSheetVisible
260     Sheet1.Visible = xlSheetVisible
270     Sheet3.Visible = xlSheetVisible
280     Sheet4.Visible = xlSheetVisible
290     Sheet5.Visible = xlSheetVisible
300     Sheet8.Visible = xlSheetVisible
310     Sheet9.Visible = xlSheetVisible
320     Sheet13.Visible = xlSheetVisible
330     Sheet16.Visible = xlSheetVisible
340     Select Case MsgBox("Current mode is: " & Sheet8.range("B27").Value _
                         & vbCrLf & "" _
                         & vbCrLf & "Do you want to change it?" _
             , vbYesNo Or vbExclamation Or vbDefaultButton1, "Mode change")
        Case vbYes
350         DeveloperFrm.Show
360     Case vbNo
370     End Select
380     ActiveWindow.DisplayWorkbookTabs = True
390     Exit Sub
400 End If
410 MyPassword = InputBox("Please enter the password", "Password Prompt")
420 If MyPassword = "*********" Then
430     Sheet4.Activate
440     Sheet4.Visible = xlSheetVisible
450     Sheet1.Visible = xlSheetVisible
460     Sheet3.Visible = xlSheetVisible
470     Sheet4.Visible = xlSheetVisible
480     Sheet5.Visible = xlSheetVisible
490     Sheet8.Visible = xlSheetVisible
500     Sheet9.Visible = xlSheetVisible
510     Sheet13.Visible = xlSheetVisible
520     Sheet16.Visible = xlSheetVisible
530     Select Case MsgBox("Current mode is: " & Sheet8.range("B27").Value _
                         & vbCrLf & "" _
                         & vbCrLf & "Do you want to change it?" _
             , vbYesNo Or vbExclamation Or vbDefaultButton1, "Mode change")
        Case vbYes
540         DeveloperFrm.Show
550     Case vbNo
560     End Select
570     ActiveWindow.DisplayWorkbookTabs = True
580 Else
590     Sheet4.Activate
600     Sheet4.Visible = xlSheetVisible
610     Sheet1.Visible = xlSheetHidden
620     Sheet3.Visible = xlSheetHidden
630     Sheet5.Visible = xlSheetHidden
640     Sheet8.Visible = xlSheetHidden
650     Sheet9.Visible = xlSheetHidden
660     Sheet13.Visible = xlSheetHidden
670     Sheet16.Visible = xlSheetHidden
680     ActiveWindow.DisplayWorkbookTabs = False
690     Application.ScreenUpdating = True
700     MsgBox "Wrong Password", vbCritical, "Access Denied"
710     Exit Sub
720 End If
    Exit Sub
HandleError:
    ErrorHandle Err, Erl(), "DeveloperAccess - RibbonMOD"
    Resume Next
End Sub

'Callback for customButton8 onAction
Sub CloseDoc(control As IRibbonControl)
    On Error GoTo HandleError
10  Application.ScreenUpdating = False
20  Application.DisplayAlerts = False
30  ResetSheets
40  ThisWorkbook.Save
50  Application.ScreenUpdating = True
60  If Workbooks.Count = 1 Then
70      Application.Quit
80      ActiveWorkbook.Close SaveChanges:=False
90  Else
100     ActiveWorkbook.Close SaveChanges:=False
110 End If
120 Application.DisplayAlerts = True
    Exit Sub
HandleError:
    ErrorHandle Err, Erl(), "CloseDoc - RibbonMOD"
    Resume Next
End Sub

Sorry about the way this has pasted, I can't seem to get it to appear any tidier than that!

I haven't yet decided which buttons will need to be disabled or hidden, or indeed what the criteria will be, but if anyone can at least start me off I should be able to work it through from there.

 

[sharky12345]

*bump*

 

[Worf]

Hi

o My example is based on the workbook from the first link.The trick is to have button names that allow collective filtering.
o If you lose the ribbon state, use the solution on the second link.

Sub EnableControlsWithCertainTag1()
'Enable only the controls with a Tag that start with "Group1"
' This means Group2 is disabled.
If ActiveSheet.[k9] = "group1" Then RefreshRibbon Tag:="Group1*"
End Sub

https://www.rondebruin.nl/win/s2/win013.htm

https://www.rondebruin.nl/win/s2/win015.htm<strike></strike>

<strike></strike>

 

[sharky12345]

Hi Worf, thanks, I have made progress and got it working, sort of. What I can't work out is how to enable or disable depending on something, (i.e. a cell value or userform button press). What I seem to have at the moment is the routine disabling the button if it's enabled and vice versa. Any help is appreciated!

 

[RoryA]

Assuming you have setup the relevant callbacks (getEnabled/getVisible) you need them to return the value you want - usually done using a module level variable that is set according to whatever criteria you have.

 

[sharky12345]

Thanks Rory - I'm not sure what's going wrong now but every time I open the file it crashes, but if I open it a 2nd time it generates a 'recovered' file which opens file. Until I can get it stable there is little point in me trying to work out how to enable or disable on command.

 

[Worf]

Hi Worf, thanks, I have made progress and got it working, sort of. What I can't work out is how to enable or disable depending on something, (i.e. a cell value or userform button press). What I seem to have at the moment is the routine disabling the button if it's enabled and vice versa. Any help is appreciated!

Post 3 shows how to do it depending on cell value. Here is the workbook:


https://www.dropbox.com/s/0oznkfnmjnydctw/EnableDisableTest.xlsm?dl=0

 

[My Aswer Is This]

I'm just curious. I have been following this thread for several weeks now. Why would a Userform not be a lot easier to use as a custom ribbon. I have a Userform in my Personal Workbook that I use as a custom Ribbon. And with the Userform being opened Modeless I can work on my Worksheet with the Userform displayed.

I have about a dozen Multipage Pages which categories my scripts and Option Buttons I click on to run my scripts. Using a Multipage you can only see the Option buttons you want at any particular time.

But I'm sure you know how that would work. So how is a custom ribbon like your building more to your liking. Building a custom ribbon using a userform uses very simple coding. For example I could easily write code to hide or unhide a Option Button if I wanted or hide the entire Multipage page.

I'm just wondering.

Maybe you would like to share a image of your custom ribbon. I would like to see it.

Not trying to be noisy just wondering. Take care