VBA CheckBox If True, Display Date / Time

melq64

New Member
Joined
Aug 21, 2017
Messages
49
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:)
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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?
 
Upvote 0
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
 
Last edited:
Upvote 0
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)
 
Upvote 0
What code have you written already?
 
Upvote 0
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
 
Upvote 0
This is what I've come up with for the "Decline" checkbox:
Code:
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....
Code:
        .Range("J34").Value = Environ("Username") & " " & Now()
Code:
        .Range("J34").Value =Environ("Username") & " " & vbNullString
 
Last edited:
Upvote 0
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
 
Upvote 0
@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...
Code:
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...
Code:
Sheets("Sheet1").Protect
Follow it with the quoted password if you set one up for this protection.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top