Lock cell linked to checkbox when checkbox is ticked

**simeony003** · 07-30-2013, 08:03 AM

Hello,

I have a spreadsheet where all cells within column E have a checkbox within them. I am looking to link the checkbox to a macro so that when the checkbox is ticked the macro will fill the cell with the user name, date and time and then lock that cell.

So far I have a macro for the user details but am unable to also lock the cell.

Please can you help.

My VBA is:

Sub CheckBox1_Click()
Dim cBox As CheckBox
Dim lRow As Integer
Dim LRange As String

LName = Application.Caller
Set cBox = ActiveSheet.CheckBoxes(LName)

lRow = cBox.TopLeftCell.Row
LRange = "E" & CStr(lRow)

If cBox.Value > 0 Then
ActiveSheet.Range(LRange).Value = Format(Date, "dd-mmm") & " " & Format(Time, "hh:mm") & " by " & (Application.UserName)

Else

ActiveSheet.Range(LRange).Value = Null
End If

End Sub

Thanks

Simone

**6StringJazzer** · 07-30-2013, 08:41 AM

Hi Simone,

Please edit your post to re-insert your code and mark it using code tags, then PM me and I (or someone else) can help you out. See forum rule below:

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

**simeony003** · 07-30-2013, 10:01 AM

Hello,

Sorry didn't realise I could add those codes in. I think i have added them now, although let me know if I need to change anything else.

Sub CheckBox1_Click()
Dim cBox As CheckBox

Please Login or Register  to view this content.

Dim lRow As Integer

Please Login or Register  to view this content.

Dim LRange As String

Please Login or Register  to view this content.

LName = Application.Caller

Please Login or Register  to view this content.

Set cBox = ActiveSheet.CheckBoxes(LName)

Please Login or Register  to view this content.

lRow = cBox.TopLeftCell.Row

Please Login or Register  to view this content.

LRange = "E" & CStr(lRow)

Please Login or Register  to view this content.

If cBox.Value > 0 Then

Please Login or Register  to view this content.

ActiveSheet.Range(LRange).Value = Format(Date, "dd-mmm") & " " & Format(Time, "hh:mm") & " by " & (Application.UserName)

Please Login or Register  to view this content.

Else

Please Login or Register  to view this content.

ActiveSheet.Range(LRange).Value = Null

Please Login or Register  to view this content.

End If

End Sub

Thanks

Simone

**6StringJazzer** · 07-30-2013, 10:08 AM

You should have edited your first post rather than reposting the material again. Whatever you did did not work. You can either type in the codes described in my post above, or select all of your code then click the # symbol in the controls above your post edit area, which will insert the codes for you.