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

 

[melq64]

Thanks. I decided to enter password and add the necessary code as you pointed out but I get the following error: "Run-time error '9' Subscript out of range"

Private Sub OBApprove_Change()
Sheets("Sheet1").Unprotect Password:="test"


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

Sheets("Sheet1").Protect Password:="test"

End Sub


Private Sub OBApprove_ClicJ()
Sheets("Sheet1").Unprotect Password:="test"


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

Sheets("Sheet1").Protect Password:="test"


End Sub


Private Sub OBDecline_Change()
Sheets("Sheet1").Unprotect Password:="test"


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

Sheets("Sheet1").Protect Password:="test"

End Sub


Private Sub OBDecline_ClicJ()
Sheets("Sheet1").Unprotect Password:="test"


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

Sheets("Sheet1").Protect Password:="test"


End Sub


Private Sub OBDefer_Change()
Sheets("Sheet1").Unprotect Password:="test"


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

Sheets("Sheet1").Protect Password:="test"


End Sub


Private Sub OBDefer_ClicJ()
Sheets("Sheet1").Unprotect Password:="test"
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

Sheets("Sheet1").Protect Password:="test"


End Sub

 

[melq64]

I found issue and fixed it. Changed "Sheet1" to name of my sheet.

Thanks again for your help, much appreciated

 

[Rick Rothstein]

I found issue and fixed it. Changed "Sheet1" to name of my sheet.

Thanks again for your help, much appreciated

Sorry, I had forgotten to change the sheet name in my copy/paste... glad you figured it out on your own.

 

[sykes]

Hi Rick

For info, I'd looked at OptionButtons, but decided that the OP may well want the option to have none of them selected, which I don't think is an option for OptionButtons, ironically!

 

[melq64]

Hi Sykes, just wanted to say thank you as well for your help initially. I couldn't get the checkbox code to work in 'Module' so I decided to go with the Options buttons.

By the way I have a last question (hopefully) if its possible to clear selection of the option buttons (i.e. no options selected) so that when file is reopened, there won't be any options pre-selected. Hope that made sense.

 

[sykes]

I'll let Rick finish off the thread, now he's dealing with your request.
BTW, I've since had a play, and found that programmatically, you CAN have all option buttons deselected at the same time - by just setting their values to false.