lundi 13 avril 2020

getting error while comparing two check boxes in vba macro

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.

enter image description here

but if I want to put some conditions as below, it is giving error. Conditions are:

  1. If the first checkbox is unchecked but the second is checked, the sheet should remain protected.
  2. If the first checkbox is checked but the second is unchecked, the sheet should get unprotected.
  3. 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