Checboxes and timestamps

D

Danny619

New member
Joined
Mar 19, 2018
Messages
4
Reaction score
0
Points
0
Hello Excelgurus,

I am new to this forum and I am posting my 1question. I started using this forum from last few weeks and it's very useful for me for my MSExcel related projects.

I have some question related to this thread. I am using Checkmark and next right cell populated with date and timestamp with this code by creating a Module in VBA.So, Bs are my checkboxes column and Cs are Date & timestamp column.

Code: 
[FONT=Verdana]Sub CheckBox_Date_Stamp()[/FONT]
[FONT=Verdana]Dim xChk As CheckBox[/FONT]
[FONT=Verdana]Set xChk = ActiveSheet.CheckBoxes(Application.Caller)[/FONT]
[FONT=Verdana]With xChk.TopLeftCell.Offset(, 1)[/FONT]
[FONT=Verdana]If xChk.Value = xlOff Then[/FONT]
[FONT=Verdana].Value = ""[/FONT]
[FONT=Verdana]Else[/FONT]
[FONT=Verdana].Value = Date & " " & Time[/FONT]
[FONT=Verdana]End If[/FONT]
[FONT=Verdana]End With[/FONT]
[FONT=Verdana]End Sub[/FONT]
[FONT=Verdana]
Also, this works perfectly fine when clicking the checkbox and its populate the date & timestamp and will not allow me to edit it back. [/FONT]

[FONT=Verdana]Private Sub Worksheet_Change(ByVal Target As Range)[/FONT]
[FONT=Verdana]Me.Protect userinterfaceonly:=True, Password:="Pass123"[/FONT]
[FONT=Verdana]Set CellsToLock = Intersect(Target, Range("C9:C20"))[/FONT]
[FONT=Verdana]If Not CellsToLock Is Nothing Then[/FONT]
[FONT=Verdana]  For Each cll In CellsToLock.Cells[/FONT]
[FONT=Verdana]    cll.Locked = True[/FONT]
[FONT=Verdana]  Next cll[/FONT]
[FONT=Verdana]End If[/FONT]
[FONT=Verdana]End Sub
But, when I uncheck the box the date & timestamp goes away, that's not what i want. I want to use the same condition for checkbox column as well (Just allow one time editable) tried with "Set CellsToLock = Intersect(Target, Range("B9:B20,C9:C20")) but its not working.
Its still allow me to check and uncheck more than one time...Can you please help me with that.

Thanks
Danny

Edit Mod : post related to https://www.excelguru.ca/forums/showthread.php?6988-One-time-permission-to-edit-cell-in-excel/page2 [/FONT]
 
Last edited by a moderator:
Hello P45cal.. I have posted some question related to your Above answer.. please help me with that
 
Hell Danny and welcome
please do not hijack exiting threads but instead create a new one ( I did it for you this time), eventually adding a link to any relevant post. This will ensure faster and better answers.
Also please wrap code with code tags ( click " Go advanced - manage attachments - select code - click the # button) I also did it for you
Thanks
 
Sorry, I didn't mean to hijack the existing thread. Thanks for your suggestions and creating the new thread for me.

Thanks
Danny
 
try:
Code: 
Sub CheckBox_Date_Stamp()
Dim xChk As CheckBox
Set xChk = ActiveSheet.CheckBoxes(Application.Caller)
With xChk.TopLeftCell.Offset(, 1)
  If xChk.Value = xlOn Then
    If Not IsDate(.Value) Then
      .Value = Date & " " & Time
      xChk.Enabled = False
    End If
  End If
End With
End Sub
and
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Me.Protect userinterfaceonly:=True, Password:="Pass123"
Set CellsToLock = Intersect(Target, Range("C9:C20"))
If Not CellsToLock Is Nothing Then
  For Each cll In CellsToLock.Cells
    cll.Locked = True
  Next cll
End If
End Sub
 
Thank you for the prompt response. I'll test and let you know.

Thanks
 
You must log in or register to reply here.
Back
Top