Ribbon - toggle button problems

[Rolly_Sefu]

Try this in the workbook that you sent me:
1. open workbook
2. go to sheet 1, press lock
3. go to sheet 2, press lock
4. go to sheet 3, press lock
5. just switch between the sheets, go from sheet 1 to sheet 2 to sheet 3.
The buttons state does not update ( one or more sheets have the two buttons at state false )

[Paul_Hossler]

try this

i cleand up the logic and code a little more

if you make a manual protection change, i don't know of any way to update the ribbon, but if you switch out and back in it should update

[Rolly_Sefu]

Hello
Thanks a lot, now it works perfectly.
I commented this line: Err.Clear '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Because the Err.Clear removed the error number witch i used to allow specific people to lock or unlock the sheets.
And with the line active the msgbox's did not appear if user was not found in the first sheet.
But now it works perfectly, thank you very much

[Paul_Hossler]

Try this version

I added a Workbook_Open and cleanup up some other code

I "think' it fixes those two issues

[Rolly_Sefu]

Hello, works perfectly, thank you.

[Paul_Hossler]

Hello
I commented this line: Err.Clear '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Yes, I used that to bypass the password checking since I was only interested in getting the buttons to work the way you wanted

[Rolly_Sefu]

Hy, this is regarding to the same thread, same file.

I need to modify the code a little bit:
- when i click on the lock button, it will password protect all cells in the sheet.
- when i click on the unlock button, i want to unprotect the sheet, unlock only column H, then protect the sheet and switch the toggle button to unlocked

I modified the code for the column H part, but i just can't get the toggle button to stay on unlocked.
The code below resets the toggle buttons
If i remove the L1="Yes" from the end when i click on unprotect it will just turn on the lock toggle button.

Option Explicit

Public gobjRibbon As IRibbonUI
 
Public Sub OnRibbonLoad(ribbon As IRibbonUI)
Set gobjRibbon = ribbon
gobjRibbon.Invalidate
End Sub


'Callback for customButton1 onAction
Public Sub Pushed(Control As IRibbonControl, pressed As Boolean)
Dim user As String, X As String, Y As String
user = Environ("Username")
On Error Resume Next
X = Application.WorksheetFunction.VLookup(user, Sheets("Stuff").Range("Login"), 1, False)
Select Case Control.ID
    Case "tbLocked"
        If Err.Number > 0 Then
            MsgBox "I'm sorry, you do NOT have access to LOCK this sheet !", vbCritical
        Else
            On Error GoTo 0
            If ActiveSheet.Name <> "WRS AR employees" Then
                ActiveSheet.Protect Password:=pwd, UserInterfaceOnly:=True, DrawingObjects:=False, Contents:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True
                Columns("H:H").EntireColumn.Locked = True
                ActiveSheet.Range("L1") = "No"
            Else
                MsgBox "I'm sorry, you do NOT have access to LOCK this sheet !", vbCritical
            End If
        End If
    Case "tbUnlocked"
        If Err.Number > 0 Then
            MsgBox "I'm sorry, you do NOT have access to UNLOCK this sheet !", vbCritical
        Else
            On Error GoTo 0
            If ActiveSheet.Name <> "WRS AR employees" Then
                ActiveSheet.Unprotect Password:=pwd
                On Error Resume Next
                Y = Application.WorksheetFunction.VLookup(user, Sheets("Stuff").Range("Access"), 1, False)
                If Err.Number = 0 Then
                    Columns("H:H").EntireColumn.Locked = False
                    ActiveSheet.Protect Password:=pwd, UserInterfaceOnly:=True, DrawingObjects:=False, Contents:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True
                    ActiveSheet.Range("L1") = "Yes"
                End If
                On Error GoTo 0
            Else
                MsgBox "I'm sorry, you do NOT have access to UNLOCK this sheet !", vbCritical
            End If
        End If
End Select
gobjRibbon.Invalidate
End Sub


'Callback for customButton1 getPressed
Public Sub UpOrDown(Control As IRibbonControl, ByRef returnedVal)
If ActiveSheet.Range("L1") = "Yes" Then
    returnedVal = Not ActiveSheet.ProtectContents
    Exit Sub
End If
Select Case Control.ID
    Case "tbLocked"
        returnedVal = ActiveSheet.ProtectContents
    Case "tbUnlocked"
        returnedVal = Not ActiveSheet.ProtectContents
End Select
End Sub

Any ideas ?
Thank you.

[Paul_Hossler]

It's a whole lot easier if you can post small sample workbook with enougth sheets to show problem

[Rolly_Sefu]

Hello

I have attached a file.

So the modification to the previous version is that:

1. on lock => all cells are locked ( lock toggle button ON )
2. on Unlock => all cells are locked, EXCEPT cells in column H ( unlock toggle button ON )

The problem is that with the modification i have made when i click on unlock, the 2 toggle buttons deselect

Any ideas ?
Thanks.

[Paul_Hossler]

I didn't trace through the whole thing, but I think that the sheet lunie is always protected

[OVT Locked] protects the entire sheet, including col H
[OVT Unlocked] protects the entire sheet, except col H

So "

ActiveSheet.ProtectContents" is always true

It might be better to just test if Col H is locked instead: "ActiveSheet.range("H:H").locked"


I didn't understand the L1 part

'Callback for customButton1 getPressed
Public Sub UpOrDown(Control As IRibbonControl, ByRef returnedVal)

'If ActiveSheet.Range("L1") = "Yes" Then
'    returnedVal = Not ActiveSheet.ProtectContents
'   Exit Sub
'End If

Select Case Control.ID
    Case "tbLocked"
        returnedVal = ActiveSheet.Range("H:H").Locked
    Case "tbUnlocked"
        returnedVal = Not ActiveSheet.Range("H:H").Locked
End Select
End Sub

[Rolly_Sefu]

Hello,

The L1 part was just a noob thinking ... did not think to check directly if column H is locked or not ( i removed all L1 lines )

I tryed the lines "returnedVal = ActiveSheet.Range("H:H").Locked" and they work perfectly.
Exactly what i needed.

Many thanks.

[Paul_Hossler]

Here's a more cleaned version if you want it

I added button labels to make it a little more explainitory

[Rolly_Sefu]

It's great, thank you