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.