VBA CheckBox If True, Display Date / Time

[melq64]

Hi newbie question please. I'm trying to create vba for checkbox when checkbox = true, display date/time else display blank/null

Thanks in advance

 

[sykes]

Try to give enough information in your question, for Folk to help you out, without having to ask for more detail.

Where's the checkbox - on a worksheet, or in a usereform?
Where do you want the date/time/blank/null to be displayed?
What's the name of the checkbox?

 

[melq64]

Sorry my bad.

1. checkbox on a worksheet
2. date/time, null to be displayed in a cell (j34)
3. name of checkbox is CheckBoxApprove

FYI - I have 3 checkboxes i.e. Approve, Defer, Decline so when any of the checkboxes are TRUE (ticked), cell j34 will display date/time else null

Hope that makes sense. Let me know if u need more detail.

Thanks

 

[melq64]

Sorry my bad.

1. checkbox on a worksheet
2. date/time, null to be displayed in a cell (j34)
3. name of checkbox is CheckBoxApprove

FYI - I have 3 checkboxes i.e. Approve, Defer, Decline so when any of the checkboxes are TRUE (ticked), cell j34 will display date/time else null

Hope that makes sense. Let me know if u need more detail.

Thanks

Also if possible, the following conditions need to apply:

1. in addition to date/time, include username to be displayed next to date/time cell
2. only 1 checkbox can be selected at a time
3. username & date/time cells cannot be edited (locked)

 

[sykes]

What code have you written already?

 

[melq64]

What code have you written already?

Private Sub CheckBoxApprove_Click()
If CheckBoxApprove.Value = True Then
CheckBoxApprove.TopLeftCell.Offset(0, 3).Value = Now
CheckBoxApprove.TopLeftCell.Offset(0, 2).Value = Environ("Username")
Else
CheckBoxApprove.TopLeftCell.Offset(0, 3).Value = vbNullString
CheckBoxApprove.TopLeftCell.Offset(0, 2).Value = vbNullString
End If
End Sub


Private Sub CheckBoxDecline_Click()
If CheckBoxDecline.Value = True Then
CheckBoxDecline.TopLeftCell.Offset(0, 2).Value = Now
CheckBoxDecline.TopLeftCell.Offset(0, 1).Value = Environ("Username")
Else
CheckBoxDecline.TopLeftCell.Offset(0, 2).Value = vbNullString
CheckBoxDecline.TopLeftCell.Offset(0, 1).Value = vbNullString
End If
End Sub


Private Sub CheckBoxDefer_Click()
If CheckBoxDefer.Value = True Then
CheckBoxDefer.TopLeftCell.Offset(0, 2).Value = Now
CheckBoxDefer.TopLeftCell.Offset(0, 1).Value = Environ("Username")
Else
CheckBoxDefer.TopLeftCell.Offset(0, 2).Value = vbNullString
CheckBoxDefer.TopLeftCell.Offset(0, 1).Value = vbNullString
CheckBoxDecline.TopLeftCell.Offset(0, 1).Value = vbNullString
End If
End Sub

 

[sykes]

This is what I've come up with for the "Decline" checkbox:

Sub CheckBoxDecline_Click()
With Me
    If .CheckBoxDecline.Value = False Then GoTo all_false
        .CheckBoxApprove.Value = False
        .CheckBoxDefer.Value = False
        .Range("J34").Value = Now()
        .Range("I34").Value = Environ("Username")
    Exit Sub
all_false:     If Me.CheckBoxApprove.Value = False And Me.CheckBoxDefer.Value = False Then
        .Range("J34").Value = vbNullString
        .Range("I34").Value = Environ("Username")
    End If
End With
End Sub

It'll need to go into the worksheet's code module (due to my use of the "Me" keyword).

If you modify it for the other two CheckBoxes (make them the same, but change the checkbox references, of course) it should all work as requested - apart from the locking of the cells; I've run out of time, so you'll need to add that for yourself. From memory, locking cells only takes effect if the sheet's protected, so you'll need to add a line to include "Protect"/"Unprotect" followed by the password, for the locking to take effect, but the bulk of the work's now done.

BTW you could put the date AND username in J34 if you wanted - but slicker than putting them into separate cells....

        .Range("J34").Value = Environ("Username") & " " & Now()

        .Range("J34").Value =Environ("Username") & " " & vbNullString

 

[Rick Rothstein]

2. only 1 checkbox can be selected at a time

Your coding would be so much easier if you used OptionButtons instead of CheckBoxes as what you asked for above is what they do natively.

 

[melq64]

Your coding would be so much easier if you used OptionButtons instead of CheckBoxes as what you asked for above is what they do natively.

@Rick Rothstein, thank you for the suggestion. I've chosen to use the Option Buttons with working code below but not sure on what code and where to insert it to ensure to lock cells after username and date/time cells have been displayed

Private Sub OBApprove_Change()


If OBApprove.Value = True Then
Sheets("Template").Range("I34").Value = Environ("Username")
Sheets("Template").Range("J34").Value = Now
Else
Sheets("Template").Range("I34").Value = vbNullString
Sheets("Template").Range("J34").Value = vbNullString

End If

End Sub


Private Sub OBApprove_ClicJ()
If OBApprove.Value = True Then
Sheets("Template").Range("I34").Value = Environ("Username")
Sheets("Template").Range("J34").Value = Now
Else
Sheets("Template").Range("I34").Value = vbNullString
Sheets("Template").Range("J34").Value = vbNullString

End If

End Sub


Private Sub OBDecline_Change()
If OBDecline.Value = True Then
Sheets("Template").Range("I36").Value = Environ("Username")
Sheets("Template").Range("J36").Value = Now
Else
Sheets("Template").Range("I36").Value = vbNullString
Sheets("Template").Range("J36").Value = vbNullString

End If
End Sub


Private Sub OBDecline_ClicJ()
If OBDecline.Value = True Then
Sheets("Template").Range("I36").Value = Environ("Username")
Sheets("Template").Range("J36").Value = Now
Else
Sheets("Template").Range("I36").Value = vbNullString
Sheets("Template").Range("J36").Value = vbNullString

End If
End Sub


Private Sub OBDefer_Change()
If OBDefer.Value = True Then
Sheets("Template").Range("I35").Value = Environ("Username")
Sheets("Template").Range("J35").Value = Now
Else
Sheets("Template").Range("I35").Value = vbNullString
Sheets("Template").Range("J35").Value = vbNullString

End If
End Sub


Private Sub OBDefer_ClicJ()
If OBDefer.Value = True Then
Sheets("Template").Range("I35").Value = Environ("Username")
Sheets("Template").Range("J35").Value = Now
Else
Sheets("Template").Range("I35").Value = vbNullString
Sheets("Template").Range("J35").Value = vbNullString

End If
End Sub

 

[Rick Rothstein]

@Rick Rothstein, thank you for the suggestion. I've chosen to use the Option Buttons with working code below but not sure on what code and where to insert it to ensure to lock cells after username and date/time cells have been displayed

If I understand what you want to do correctly, I think the first thing to do is select all the cells on the worksheet, call up the "Format Cells" dialog box, select the Protection tab on it and uncheck the Locked checkbox and then click the OK button. Next, select cells I34:J35 and call up the "Format Cells" dialog box, select the Protection tab and put a check in the Locked checkbox and then click the OK button. Now right-click the worksheet tab and select the "Protect Sheet" item in the popup menu that appeared. You can now either press the OK button or put a password in the password field (make sure you remember this password if you enter one) and then press the OK button. As of now, those six cells cannot be edited. In each of your code procedures, you will add this at the beginning...

Sheets("Sheet1").Unprotect

If you placed a password on the protection, you will need to include it as a quoted string on the same line of code using a space between the them. This will allow your code to modify the cells. At the end of your code, you would need to reestablish the protection using this line of code...

Sheets("Sheet1").Protect

Follow it with the quoted password if you set one up for this protection.