I have two checkboxes, the first checkbox is associated with first-person and the second checkbox is associated with the second person who is a reviewer. When checkboxes are checked, it should fetch the user name and the current time. Also, When only first checkbox is checked, sheet will be not be protected but if the second checkbox is checked, sheet should get protected. this is working fine.
but if I want to put some conditions as below, it is giving error. Conditions are:
- If the first checkbox is unchecked but the second is checked, the sheet should remain protected.
- If the first checkbox is checked but the second is unchecked, the sheet should get unprotected.
- both the checkboxes are unchecked, the sheet should get unprotected.
PLease help. Any kind of help will be very appreciated. thank you!
First checkbox
Global Cb1 As CheckBox
Global Cb2 As CheckBox
Sub checkboxNew1()
Set Cb1 = ActiveSheet.CheckBoxes(Application.Caller)
LRange = "C" & CStr(Cb1.TopLeftCell.Row)
Ltime = "D" & CStr(Cb1.TopLeftCell.Row)
'xlOn= 1 and xlOff= -4146
With Cb1.TopLeftCell.Offset(, 1)
If Cb1.Value = xlOn Then
ActiveSheet.Unprotect Password:="c1tc0"
ActiveSheet.Range(LRange).Value = Environ("USERNAME")
ActiveSheet.Range(Ltime).Value = Now
Else
ActiveSheet.Unprotect Password:="c1tc0"
ActiveSheet.Range(LRange).Value = Null
ActiveSheet.Range(Ltime).Value = Null
End If
End With
End Sub
'Second checkbox
Sub checkboxnew2()
Set Cb2 = ActiveSheet.CheckBoxes(Application.Caller)
LRange = "C" & CStr(Cb2.TopLeftCell.Row)
Ltime = "D" & CStr(Cb2.TopLeftCell.Row)
With Cb2.TopLeftCell.Offset(, 1)
If Cb2.Value = xlOn Then
ActiveSheet.Unprotect Password:="c1tc0"
ActiveSheet.Range(LRange).Value = Environ("USERNAME")
ActiveSheet.Range(Ltime).Value = Now
ActiveSheet.Protect Password:="c1tc0"
Else
ActiveSheet.Unprotect Password:="c1tc0"
ActiveSheet.Range(LRange).Value = Null
ActiveSheet.Range(Ltime).Value = Null
End If
End With
'xlOn =1 And xlOff = -4146
If Cb1 = xlOff And Cb2 = xlOn Then
ActiveSheet.Unprotect Password:="c1tc0"
ActiveSheet.Range(LRange).Value = Null
ActiveSheet.Range(Ltime).Value = Null
ActiveSheet.Protect Password:="c1tc0"
Else
End If
End Sub
Aucun commentaire:
Enregistrer un commentaire