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.
[Blocked Image: https://i.stack.imgur.com/gjVGh.png]
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!
Code
- '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